Backup error logging RRS feed

  • Question

  • 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

            BACKUP DATABASE testdb TO DISK = 'Z:\testdb\testdb_Full_20191018.BAK';
     END TRY
                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];




    Friday, October 18, 2019 6:05 AM


  • As other have said, this is not simple. Well, you can use ;THROW in the CATCH block, which re-raise all error message. But but that will also abort execution of the script - and most likely you want to move on to the next database.

    There is no good solution, but there are some kludges, and not particularly pretty ones. I have written about them in chapter seven in Part Three of my series Errors and Transaction Handling in SQL Server:

    Erland Sommarskog, SQL Server MVP,

    • Marked as answer by Samantha v Saturday, October 19, 2019 4:38 AM
    Friday, October 18, 2019 9:02 PM

All replies