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
Last activity: Mar 23, 2025, 08:41 PM