If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Wrapping backup in a TRY...CATCH block.

I'm experimenting with a new process for managing our production database backups which uses a cursor to loop through the list of databases to initiate a backup of each one.

I find that on the odd occasion one of the backups may fail for one reason or another and I don't want this to interfere with the rest of the procedure - i.e. I want the SQL server agent job to keep executing and attempt the remaining backup operations.

I've tried wrapping the BACKUP command in a TRY...CATCH block, hoping that this will enable the backup process to keep going as well as using the CATCH block to send via dbmail a meaningful error message.

What I find, when executing this, is that if the backup fails with an error - for example an OS error with an invalid directory, the procedure itself generates an error and then sends the mail, so executing the stored procedure I get the following:
-----
Msg 3201, Level 16, State 1, Procedure Maintenance_DB_Backup, Line 76
Cannot open backup device 'D:\BackupDir\LogBackup.bck'. Operating system error 3(The system cannot find the path specified.).
Mail queued.
-----
With an email generated with the ERROR_MESSAGE text of:
"Error 3013 - [BACKUP DATABASE is terminating abnormally.]"

Does anyone have any thoughts on how I can get this TRY...CATCH block to send me the actual error message as well as getting the stored procedure to not throw an error. The procedure itself continues on to the next backups, so the catch in effect is working correctly, however the purpose of this is to have some meaningful information sent to the DBA's so we can look at why the backups have failed. With 10 or 15 backups being taken each night the SQL agent job step log fills up and gets truncated so we can never see the actual error.

Thanks george, but if you look at my code example you will see that I am already using the error_* calls, and I am assigning them to variables, my problem is that they do not contain the actual error, rather backup appears to wrap the error in its own error catching block and pass a generic backup failed error back to the calling procedure which is of no help to anyone really.

I'd love to find a good use for TRY CATCH, but so far have not come up with one. The only place I've really wanted to implement it so far was in a trigger, which turns out to be verboten.

Up until now I hadn't found a valid reason for using it either - but I can't think of any other way to ensure that a failure in one of the backups undertaken by the stored procedure doesn't result in no further backups being taken.