Sample Header Ad - 728x90

How to log error details when using using try/catch for dynamic SQL backup commands

10 votes
4 answers
21405 views
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.
Asked by crokusek (2110 rep)
Jun 25, 2012, 07:41 PM
Last activity: Mar 23, 2025, 08:41 PM