Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Wrapping backup in a TRY...CATCH block.

    I'm experimenting with a new process for managing our production database backups which uses a cursor to loop through the list of databases to initiate a backup of each one.

    I find that on the odd occasion one of the backups may fail for one reason or another and I don't want this to interfere with the rest of the procedure - i.e. I want the SQL server agent job to keep executing and attempt the remaining backup operations.

    I've tried wrapping the BACKUP command in a TRY...CATCH block, hoping that this will enable the backup process to keep going as well as using the CATCH block to send via dbmail a meaningful error message.

    The relevant part of the code is as follows:
    Code:
    BEGIN TRY
    	BACKUP LOG @v_DatabaseName TO DISK = @v_BackupFileLocation WITH NAME = @v_BackupFileName, SKIP, STATS = 10
    END TRY
    BEGIN CATCH
    	Set @v_ErrorMailSubject = 'Transaction log backup failure in database '+@v_DatabaseName
    	Set @v_ErrorMailText = 'An error occured in transaction log backup for database '+@v_DatabaseName+'.'+Char(13)+Char(13)
    	Set @v_ErrorMailText = @v_ErrorMailText + 'Error '+Cast(Error_Number() as nvarchar)+' - ['+Error_Message()+']'
    	exec msdb.dbo.sp_send_dbmail	@recipients=@v_ErrorMailRecipient, 
    					@subject = @v_ErrorMailSubject,
    					@body = @v_ErrorMailText
    END CATCH
    What I find, when executing this, is that if the backup fails with an error - for example an OS error with an invalid directory, the procedure itself generates an error and then sends the mail, so executing the stored procedure I get the following:
    -----
    Msg 3201, Level 16, State 1, Procedure Maintenance_DB_Backup, Line 76
    Cannot open backup device 'D:\BackupDir\LogBackup.bck'. Operating system error 3(The system cannot find the path specified.).
    Mail queued.
    -----
    With an email generated with the ERROR_MESSAGE text of:
    "Error 3013 - [BACKUP DATABASE is terminating abnormally.]"

    Does anyone have any thoughts on how I can get this TRY...CATCH block to send me the actual error message as well as getting the stored procedure to not throw an error. The procedure itself continues on to the next backups, so the catch in effect is working correctly, however the purpose of this is to have some meaningful information sent to the DBA's so we can look at why the backups have failed. With 10 or 15 backups being taken each night the SQL agent job step log fills up and gets truncated so we can never see the actual error.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look at the BOL entry for try...catch: TRY...CATCH (Transact-SQL)

    Snippet from the link
    Code:
    BEGIN TRY
        -- Generate a divide-by-zero error.
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    You can assign things like the error message to variables in a similar vein
    Code:
    DECLARE @msg varchar(1000)
          , @num int
    
    SELECT @msg = Error_Message()
         , @num = Error_Number()
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd love to find a good use for TRY CATCH, but so far have not come up with one. The only place I've really wanted to implement it so far was in a trigger, which turns out to be verboten.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by gvee
    Look at the BOL entry for try...catch: TRY...CATCH (Transact-SQL)

    Snippet from the link
    Code:
    BEGIN TRY
        -- Generate a divide-by-zero error.
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    You can assign things like the error message to variables in a similar vein
    Code:
    DECLARE @msg varchar(1000)
          , @num int
    
    SELECT @msg = Error_Message()
         , @num = Error_Number()
    Thanks george, but if you look at my code example you will see that I am already using the error_* calls, and I am assigning them to variables, my problem is that they do not contain the actual error, rather backup appears to wrap the error in its own error catching block and pass a generic backup failed error back to the calling procedure which is of no help to anyone really.

  5. #5
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by blindman
    I'd love to find a good use for TRY CATCH, but so far have not come up with one. The only place I've really wanted to implement it so far was in a trigger, which turns out to be verboten.
    Up until now I hadn't found a valid reason for using it either - but I can't think of any other way to ensure that a failure in one of the backups undertaken by the stored procedure doesn't result in no further backups being taken.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •