SQL Backup fails to send e-mail if Job fails
cousinsp
Posts: 29
I've just had a slightly embarrassing situation whereby the SQL Agent job to run the Redgate Backups on a server have been failing for some time, but no error e-mail has been sent. This is because the job is failing to run, due to an odd permissions problem (SQL can't work out whether I, as owner of the job, have permissions to run the job or not). I'm wondering if it would be possible to change SQL Backup Pro, so that there is an option for SQL Agent to send an error e-mail, if the job fails - as per
the options on the 'notifications' tab.
I've solved the problem on this server, by changing the owner of the job to 'sa', and manually altering the SQL Agent job to e-mail us if it fails.
In the longer term I hope to buy an Enterprise Scheduler to schedule SQL Backups - this will warn us if the job fails (no matter the reason), and even if the job doesn't even attempt run.
the options on the 'notifications' tab.
I've solved the problem on this server, by changing the owner of the job to 'sa', and manually altering the SQL Agent job to e-mail us if it fails.
In the longer term I hope to buy an Enterprise Scheduler to schedule SQL Backups - this will warn us if the job fails (no matter the reason), and even if the job doesn't even attempt run.
Pete Cousins
Comments
As you know, SQL Backup scheduled jobs relies upon the SQL Server Agent to run the commands to call the SQL Backup Extended Stored Procedures and then the task runs.
The SQL Backup e-mail notification, will only sent an e-mail if:
1. The e-mail server settings are configured via the SQL Backup GUI ->Server Options.
2. The MAILTO or MAILTO_ONERROR or MAILTO_ONERRORONLY keywords are included.
If the SQL Server Agent is unable to start or call the SQL Backup Extended Stored Procedures no e-mail will be sent by SQL Backup.
However, you can setup or configure SQL Server to generate e-mail and notifications to alert yourself and colleagues that a job did not start or run successfully.
Another option is to use a SQL Server monitoring solution, for example our own SQL Monitor, which will alert and send e-mail notifications that SQL Server Agent job did not start or run. It will also alert you, if the SQL server agent service is in a stopped condition.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com