Inserting Results from Stored Proc into another Database Table

  • Hi All,

    I am currently going through the internet and finding some solutions on backing up while using SQL Express. As Express doesn't have agent/maintenance plan I'm trying to create a similar process.

    I've found some solutions and modified it to work in our environment, so full backup gets created and does a check if older than 3 days it will delete it therefore keeping only latest 3 backups at a time.

    The above works successfully, but what im aiming for now is when backup is successful to insert the results into another Database/Table something as simple as the filename & date but i am having trouble with this and i can't get the results to be inserted.

    Script to create the stored proc is below followed by the executing of the script.

    ALTER PROCEDURE [dbo].[sp_BackupDatabase]

    @databaseName sysname, @backupType CHAR(1)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @sqlCommand NVARCHAR(1000)

    DECLARE @dateTime NVARCHAR(20)

    SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','')

    IF @backupType = 'F'

    SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +

    ' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

    IF @backupType = 'D'

    SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +

    ' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'

    IF @backupType = 'L'

    SET @sqlCommand = 'BACKUP LOG ' + @databaseName +

    ' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''

    EXECUTE sp_executesql @sqlCommand

    END

    to execute the script im using the command

    exec sp_BackupDatabase 'master', 'F'

    exec sp_BackupDatabase 'model', 'F'

    Now i want to insert the results into my new database "BACKUP" which has table called "EventLog" with 3 columns "ID, Backup & Logged"

    Thats where im stuck and i can't get the results in there?

    Any ideas please help???

    Thanks

    Tava

  • to add the insert you just need to change each block of code like the one below. Also i dont think you need the dynamic SQL as i believe you can use variables for the file name and database name. just make sure to declare them when you start your SP

    IF @backupType = 'F'

    BEGIN -- Change to your original code

    SET @File = '''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

    BACKUP DATABASE @databaseName

    TO DISK = @File

    ---the first if checks if there were errors. you could use an insert similar to the one below

    --but insert the error into your backup table

    IF @@Error <> 0

    SELECT 'Some Error Text' AS ERROR

    ELSE

    INSERT INTO dbname.dbo.TableNAME (Col1,Col2) VALUES (Col1Value, Col2Value)

    END

    just copy the above for each of your if statements for the type of backup and change the insert and backup database command to match your needs. you may not need the lead and trail ' on the file name though. im not at my desktop to test 100%


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Why are you not using msdb database. It will have all your backup information stored in the system tables.

    select * from backupfile

    select * from backupset

    Check out these tables.

  • Hi All,

    Just realised i havent answered/closed this topic.

    I ended up getting it working by using a Try/Catch statement and inserted it into the relevant table depending on the result. ended up being pretty simple.

    Why i dont use the msdb backup information thats available - not really sure information i need is pretty much all there.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply