SQL Server: Is it possible to prevent SQL Agent from failing a step on error?
- by franklinkj
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 this is possible in SQL 2000 or if I need to go in a completely different direction.
