Right now I have daily (differential) and weekly (full) backups set on my SQL Server 2008 R2 as jobs for SQL Server Agent with email notifications if job fails. I do get emails like this:
JOB RUN: 'Daily backup.Diff backup' was run on 4/11/2012 at 3:00:00 AM
DURATION: 0 hours, 0 minutes, 28 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 9 (Daily backup.Diff backup). The last step to run was step 1 (Diff backup).
but often that happens because we delete/create new databases - and diff backup fails. And the only way for me to see the actual reason is to go to Log Viewer → Maintenance Plans logs.
Is it possible to include "Error Message" field from the logs into notification emails? And more generic - is it possible to change notification email templates somehow?
avs099, when you use the "Notifications" property of a job, you can only determine who will send the email, and when, you can't change the email body content.
But the "Notifications" page is not the only way to send and email, we have the SQL sp_send_dbmail procedure: all you need to do is modify your T-SQL backup code to call it when an error occurs.
Have you taken a look at enabling Remote Errors for the SQL Reporting Server? Connect to Reporting Server, SQL Management Studio Right click on the server node > properties > advanced, EnableRemoteErrors
There are also a number of other system properties in the advanced screen that could help you out. Let me know if you come up with something different as well!