When issuing a backup command within a stored procedure that uses a try catch and dynamic sql, the error messages are very general when compared to running the backup command directly.
Try/Catch within SP:
begin try
execute sp_executesql @sql; -- a backup command
end try
begin catch
print ERROR_MESSAGE(); -- save to log, etc.
end catch
Results in
50000:usp_Backup:117: BACKUP DATABASE is terminating abnormally.
wheareas issuing the raw command:
backup DATABASE someDb to disk...
Results in better details:
Lookup Error – SQL Server Database Error: A nonrecoverable I/O error
occurred on file "H:\FolderName\Filename.bak:" 112(There is not enough
space on the disk.).
Is there a way to catch these details into variables within the stored procedure (to log, pass back to caller, for retry logic)? It seems the details are coming through on the message channel but I would like them available within the SP.
Best Answer
When
BACKUP DATABASE
generates an error, it actually generates two. UnfortunatelyTRY/CATCH
is not capable of capturing the first error; it only captures the second error.I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with
-o
to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.The SO answer in the comment suggests using
DBCC OUTPUTBUFFER
- while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination withTRY/CATCH
.The only way I seemed to be able to capture the error message with
spGET_LastErrorMessage
is if the actual error does get thrown. If you wrap it in aTRY/CATCH
the error gets swallowed and the stored procedure does nothing:In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:
Or in 2012 and above, this works, but to a large degree defeats the purpose of
TRY/CATCH
, since the original error still gets thrown:In both of these cases, the error is still thrown to the client, of course. So if you're using
TRY/CATCH
to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.