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

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.

When BACKUP DATABASE generates an error, it actually generates two. Unfortunately TRY/CATCH is not capable of capturing the first error; it only captures the second error.

I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.

The SO answer in the comment suggests using DBCC OUTPUTBUFFER - while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH.

The only way I seemed to be able to capture the error message with spGET_LastErrorMessage is if the actual error does get thrown. If you wrap it in a TRY/CATCH the error gets swallowed and the stored procedure does nothing:

BEGIN TRY
  EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
  EXEC dbo.spGet_LastErrorMessage;
END CATCH

In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:

CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sp_executesql N'backup that fails...';
END
GO

EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;

Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH, since the original error still gets thrown:

CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    EXEC sp_executesql N'backup that fails...';
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH
END
GO

EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;

In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.

StackExchange Database Administrators Q#19895, answer score: 13