Informing the SQL Agent of job failures

Category: How do I?
Date: 05 Jun 2009
Product: SQL Backup
Versions: 4,5

When a scheduled backup job fails, it is a possibility that no failures will be reported in the SQL Server or Windows Event Logs.

If you had created a SQL Backup script yourself, and used the script in a SQL Agent job step in order to schedule the backup, you may not be notified of the failure. This is because the SQL Backup extended stored procedure does not notify the SQL Agent of job failures automatically.

If the job had been created using the SQL Backup scheduling wizard, you are notified in the case of backup failures, because SQL Backup adds extra logic to the script to trap the error codes returned by the backup and return them to the SQL Agent. This logic must also be applied if you create the script manually, for example:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [MyDB] TO DISK = ''G:\MSSQL\BACKUP\<database>\<AUTO>.sqb'' WITH COMPRESSION = 2"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0) / SQL Backup designates error codes greater that 500 as "failure" /
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

In this example, the RAISERROR command is used to notify the SQL Server Agent that the backup had failed, and inputs the error code returned by SQL Server as well as the SQL Backup error code.