Post reply

Backup Failure Reasons in Email

  • September 30, 2011 at 12:57 am

    #248998

    Hi,

    I was wondering if there is any functionality in sql server where if a job fails, it sends out an email to one of the choose agent operators with the failure reason. I can receive emails saying that backup has failed and also can write the reason to an output file but how to send the same reason in the email.

    Thanks

    Chandan

  • September 30, 2011 at 2:04 am

    #1388719

    Setting up email notifications for failed jobs is very easy. See here[/url].

    Adding the failure reason and message is a bit more complicated. Instead of relying on standard notifications, you should add your own notification routine to the job steps. If you can save the error message to a file, add a job step that sends and email with the log file as an attachment.

    Something like:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'yournotificationprofile'

    ,@recipients = 'youremailaddress'

    ,@subject = 'Backup failed'

    ,@body = 'Oh, no! Backup failed!'

    ,@file_attachments = 'c:\logfile.txt'

    You could edit the job steps options to quit the job with success when the backup job step succeeds and let it go to the notification step when the backup step fails.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • September 30, 2011 at 6:38 am

    #1388817

    Gianluca Sartori (9/30/2011)

    Setting up email notifications for failed jobs is very easy. See here[/url].

    Adding the failure reason and message is a bit more complicated. Instead of relying on standard notifications, you should add your own notification routine to the job steps. If you can save the error message to a file, add a job step that sends and email with the log file as an attachment.

    Something like:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'yournotificationprofile'

    ,@recipients = 'youremailaddress'

    ,@subject = 'Backup failed'

    ,@body = 'Oh, no! Backup failed!'

    ,@file_attachments = 'c:\logfile.txt'

    You could edit the job steps options to quit the job with success when the backup job step succeeds and let it go to the notification step when the backup step fails.

    Hope this helps

    Gianluca

    Thanks. I created a new job step in my backup job,loaded the error in a table and in that use sp_send dbmail to read from that table and send that statement in the body of email. We do not have any inbuilt mechanism for this so as you mentioned that we need to create a custom script is something we need to work on.

    Regards

    chandan

  • September 30, 2011 at 6:51 am

    #1388822

    You're welcome.

    If you need further assistance, you can come back here and ask.

    -- Gianluca Sartori

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

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