SQLBkup 32 - Making Last Run Status Failure on backup fail

Brian Donahue

• Date: 13-May-2005
• Versions: 3.1-

When creating a SQL Server 2000 job to backup databases using SQL Backup's extended stored procedure, the job status will still appear as success even if the SQL Backup job step had failed to backup the database. This can interfere with workflow if you have set the job step up to perform a certain action on failure, such as notifying an administrator.

This can be worked around, however, by checking the result code returned by the SQLBackup stored procedure and raising the appropriate error manually to indicate that the job step had failed. Passing the appropriate values to the SQL RAISERROR command can cause the job step to change the status to a failure. Here is an example SQL script that you should use as an example of a job step.

DECLARE @success int
EXEC @success=master..sqlbackup '-SQL "BACKUP DATABASE [WidgetProduction]  TO DISK = ''e:\sql\MSSQL\BACKUP\WidgetDev\FULL_(local)_WidgetDev.sqb'' WITH INIT,  NAME = ''Database (WidgetDev)'', DESCRIPTION = ''Daily Backup'', COMPRESSION = 1" -E'
/* SQLBackup retrurns 0 for failure and 1 for success */
IF @success=0
BEGIN  
RAISERROR('Backup Failed', 16,1)
END

Brian Donahue

When using the scheduling wizard and log shipping wizard in SQL Backup version 4 and up, this is not an issue. The necessary parameters are inserted into the backup job step in the SQL Agent job.

It's still an issue to consider if you are creating backup jobs manually from scratch.