SQL Server: Is it possible to prevent SQL Agent from failing a step on error?
- by Kenneth
I have a stored procedure that runs custom backups for around 60 SQL servers (mixes 2000 through 2008R2).
Occasionally, due to issues outside of my control (backup device inaccessible, network error, etc.) an individual backup on one or two databases will fail. This causes this entire step to fail, which means any subsequent backup commands are not executed and half of the databases on a given server may not be backed up.
On the 2005+ boxes I am using TRY/CATCH blocks to manage these problems and continue backing up the remaining databases. On a 2000 server however, for example, I have no way to prevent this error from failing the entire step:
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'db-diff(\PATH\DB-DIFF-03-16-2010.DIF)'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
I am simply asking if anything like TRY/CATCH is possible in SQL 2000? I realize there are no built in methods for this, so I guess I am looking for some creativity.
Even when wrapping each backup (or any failing statement) via sp_executesql the job fails instantly. Example:
DECLARE @x INT,
@iReturn INT
PRINT 'Executing statement that will fail with 208.'
EXEC @iReturn = Sp_executesql N'SELECT * from TABLETHATDOESNTEXIST;'
PRINT Cast(@iReturn AS NVARCHAR) --In SSMS this return code prints. Executed as a job it fails and aborts before this statement.
