SQLBkup 32 - Making Last Run Status Failure on backup fail
- 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