The new Try…Catch T-SQL error trapping mechanism introduced in SQL 2005 is quite handy. But it does have some perils if you don’t think things through completely.

The backup routine we use here is a single job that runs a stored procedure. The procedure loops through all the databases on the server and makes a backup of each one. I wanted to make sure that if one backup failed for some reason, the procedure would continue on backing up the remaining databases. A perfect job for Try…Catch! So we wrote that into the procedure, tested it, and it worked great! If I’ve got 10 databases on a server and the backup for the second one fails, the procedure carries on and backs up the remaining 8 databases without bombing out.

Trouble was – I never got notified of the failure. Because the stored procedure didn’t fail out, the job didn’t fail, and no error alert was sent to me. That database backup could have been failing for weeks and I’d never know. I just happened to discover this because I was manually running the job and noticed a backup wasn’t made but the job didn’t fail.

There are a couple of ways to fix this. You could change the Try…Catch block to send an email when a backup failed. I opted not to go this route. I don’t like hard coding email addresses into procedures and if I made a lookup table to get an email address, I’d need to put that table on all 60+ servers I maintain. That’s more work than I wanted to do. So instead, I added a bit variable to the stored procedure. If any backup fails, I set the bit to 1 in the Try…Catch block. At the end of the procedure, after all the backups have been completed, I check the variable. If it’s a 1, I execute a RAISERROR command, which causes the stored procedure to return a failure code. The job then fails and an alert is sent out per my standard alert setup.