SQL Server: Is it possible to prevent SQL Agent from failing a step on error?

Question

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.

Answer 1

Short answer: No.

Is there anything else you can do to try to preemptively avoid the the error? Check for connections/transactions etc. Do you know why it's considered Access is Denied (I'm assuming it's "in use").

Can the base proc be split into multiple segments of 1 per db? i.e. same proc called multiple times via a batch file or other root file?

Can you move it up a level to shell the call in another managing app or layer?

Would it be better to use a 3rd party tool like RedGates SQL Backup? The upfront expense of such a tool may more than save the cost support over time. http://www.red-gate.com/products/SQL_Backup/index.htm

Answer 2

If you have XP_CmdShell enabled, then you can use that for SQL2000 systems. You will lose some error handling, and will have to get creative around capturing input for log files.

Answer 3

I got around this by recreating job steps for each database, rather then one job that iterates through DBs.

That is, ensure you have a job step for each DB at the start of the backup. Then continue on fail.