Exception handling for backup related errors.
-
Hi All,
Need some help in exception handling. In below code, I want to know the reason why the backup is failing.
For testing purpose, I have intentionally given wrong path of the backup file. i.e. Z: drive doesn't exist and when I run the backup command alone, it gives exact error message saying path not found. However, if I run the code inside an exception block, I am not getting exact error message. How to capture the exact error message ?? If everything works fine, I want to log that error in a errorlogging table.
BACKUP DATABASE testdb TO DISK = 'Z:\testdb\testdb_Full_20191018.BAK';
/*
Msg 3201, Level 16, State 1, Line 4
Cannot open backup device 'Z:\testdb\testdb_Full_20191018.BAK';. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
*/
Running with try - catch block
BEGIN TRY
BACKUP DATABASE testdb TO DISK = 'Z:\testdb\testdb_Full_20191018.BAK';
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() AS [Error_Line],
ERROR_MESSAGE() AS [Error_Message],
ERROR_NUMBER() AS [Error_Number],
ERROR_SEVERITY() AS [Error_Severity],
ERROR_PROCEDURE() AS [Error_Procedure];
END CATCH
Thanks,
Sam
-
Unfortunately unless you do something like Ken suggested, there isn't an easy way to do it inside the CATCH block. Is the backup part of a SQL Agent job? SQL Agent would store both errors in the job step history not just the last one like CATCH does. What were you gonig to do inside the CATCH block besides just display the error info?
-
Thank you All for the suggestions. Yes, we run the backups as part of SQL Agent Job.
-
A bit late to the party here, but...
You can look into implementing Ola Hallengren's maintenance solution (https://ola.hallengren.com/) - it works beautifully.
"My gosh! It's full of [SELECT] stars!"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply