none
Capture BACKUP DATABASE t-sql command output into variable

    Question

  • Hello, everyone.

    I am rewriting my T-SQL script for database backups. I have extended it to allow e-mail sending so that I know in the e-mail´s header there was any problem with the backups on a particular server.

    I also tried to attach the SQL Server Agent output log file, so that I can read the full Job's output when I need to.

    However, there is a problem with this idea, since I am trying to read the file from inside the same T-SQL Script that does the backups.. So the file is of course not closed for writing yet and thus SQL Server cannot attach a file that it is writing to :)


    I want to solve this problem by capturing the output of each BACKUP DATABASE command into a variable and then use some sp_OAMethod to write to it. Thus, I can build a separate log file with all my information, close it and then send it as an attachment.

    How can I capture all output of each BACKUP DATABASE command? I know that sp_executesql has a way to do this but I am in need of help here...


    Any help is very appreciated.

    Thanks in advance,

    Ivo Pereira
    IT Consultant
    Portugal
    ComputerDoc
    Tuesday, November 17, 2009 8:50 PM

Answers

  • Hello again, everyone.


    Looks I came up with a solution, after all. It may not be the most elegant but it sure did solve my problem!!
    Given that I decided to parse the file, since I had no other option, I created another stored proc to read the log file and build the email message as I need it to.

    Assuming that I always configure SQL Server Agent to ouput the Job´s result to a file (as I always did and still do...) all I have to do is BULK INSERT the file into a temporary table and parse it there.


    CREATE TABLE BackupLogLines ( lines VARCHAR(MAX))
    BULK INSERT #BackupLogLines FROM 'E:\some_log_file.log'


    Now, I can do this because I changed the main Backup SP to "write" (actually just PRINT the output) a line like "### BACKUPS OK ###" or "### BACKUPS NOT OK ###" on the agent´s log file. Then, I read it from the new SP and try to find the text above. The rest is just using sp_send_dbmail (wich works great for me...seen lots of folks out there that don´t like to rely on it.... I used "blat" back in the OSQL/MDSE days) and build the subject/body/etc accordingly, attaching also the Log file wich is, by now, already closed by SQL Server Agent :)


    Hope this helps anyone with the same problem..!


    Thanks anyway for all the help,

    Ivo Pereira
    IT Consultant
    Portugal



    ComputerDoc
    Thursday, November 19, 2009 7:06 PM

All replies

  • I dont think you can capture the outcome of the backups because that information is printed to the screen.  Why dont you look in the msdb system tables to extract the backup or job information. msdb.dbo.backupset holds all the necessary information about the backup, while msdb.dbo.sysjobhistory contains all the necessary information about the success and failure of each job step on a per execution basis.



    SELECT *
    FROM msdb.dbo.[backupset]
    WHERE database_name = '';
    
    SELECT *
    FROM msdb.dbo.[sysjobhistory] jh
    INNER JOIN msdb.dbo.[sysjobs] j
    	ON [jh].[job_id] = [j].[job_id]
    WHERE j.name = ''; --JobName
     
    http://jahaines.blogspot.com/
    Tuesday, November 17, 2009 9:24 PM
  • Hello, everyone.

    Thank you very much for all the quick replies! :) It´s good to see someone lend a hand when you need.


    I could really run my Jobs outputing information to a log file and then attach it without a problem. The thing is that I dinamically build the email´s Subject inside the backup´s SP by counting the amount of errors on each BACKUP DATABASE command, so the email´s subject  is "smart" and only shows something like:

    "Backups are OK on server XXXX" or "Backups are NOT OK on server XXXX"


    (My inbox keeps gettin bigger... I have to sort through it quickly and this kind of email helps a lot :D )


    So, the conclusion is that if I log all the Job´s output (either with SQLCMD, wich I don´t really like or have to use since all my servers are 2K5 or 2K8) all the lines will be there, BUT I will have to parse the text to find all the errors/warnings/etc and build the message subject in another SP... And that I really did not want to do :(


    This may seem like some sort of "fanciness" but I keep getting more and more SQL servers to administer and I just have to automate the process to the fullest... :(
    The way I see it, if I could redirect the output of sp_executesql the problem would be solved. On each BACKUP event I would direct it to a new file line and that would be it..


    Adam, your idea is also very good, but SQL server only logs parts of the BACKUP DATABASE output... I have some servers with 30 or so databases and I really need to get an attachment with all that stuff. I forgot to mention I also run a RESTORE VERIFYONLY on each file in a loop and THAT information is also useful (more lines of text.. lol)



    Any other ideas, guys?


    Thanks a lot for the help,

    Ivo Pereira
    IT Consultant
    Portugal

    ComputerDoc
    Wednesday, November 18, 2009 7:15 PM
  • Hello again, everyone.


    Looks I came up with a solution, after all. It may not be the most elegant but it sure did solve my problem!!
    Given that I decided to parse the file, since I had no other option, I created another stored proc to read the log file and build the email message as I need it to.

    Assuming that I always configure SQL Server Agent to ouput the Job´s result to a file (as I always did and still do...) all I have to do is BULK INSERT the file into a temporary table and parse it there.


    CREATE TABLE BackupLogLines ( lines VARCHAR(MAX))
    BULK INSERT #BackupLogLines FROM 'E:\some_log_file.log'


    Now, I can do this because I changed the main Backup SP to "write" (actually just PRINT the output) a line like "### BACKUPS OK ###" or "### BACKUPS NOT OK ###" on the agent´s log file. Then, I read it from the new SP and try to find the text above. The rest is just using sp_send_dbmail (wich works great for me...seen lots of folks out there that don´t like to rely on it.... I used "blat" back in the OSQL/MDSE days) and build the subject/body/etc accordingly, attaching also the Log file wich is, by now, already closed by SQL Server Agent :)


    Hope this helps anyone with the same problem..!


    Thanks anyway for all the help,

    Ivo Pereira
    IT Consultant
    Portugal



    ComputerDoc
    Thursday, November 19, 2009 7:06 PM
  • Hello again, everyone.


    Looks I came up with a solution, after all. It may not be the most elegant but it sure did solve my problem!!


    ComputerDoc

    Nice Ivo, thanks for sharing.


    If you want to be real elegant, you build an SSIS package with the fantastic BIDS GUI designer tool.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, November 19, 2009 7:36 PM