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

Question

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.

Answer 1

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.

Answer 2

Well I know this is an old thread, and I know what I'm about to propose is a convoluted hack, but just in case it can help anyone, here goes: Since these backup errors get logged, you can use xp_readerrorlog in the catch block to scrape the log for related message (error or info). You can google around for xp_readerrorlog params but in short you can spec a search-string and a begin time filter which are useful in this case. Not sure if this would help your retry logic, but to capture either info or errors for logging, I came up with something like this...

IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (LogDate datetime,ProcessInfo nvarchar(100),LogText nvarchar(4000))
BEGIN TRY
SELECT @begintime = GETDATE()
EXEC sp_executesql @SQL --your backup statement string
INSERT #Results
EXEC  xp_readerrorlog 0, 1, N'backed up',@databasename,@begintime
SELECT @result = LogText from #Results where ProcessInfo = 'Backup' order by logdate desc
END TRY
BEGIN CATCH
INSERT #Results
EXEC  xp_readerrorlog 0, 1, N'Backup',@databasename,@begintime
SELECT @result = LogText from #Results where ProcessInfo = 'spid'+cast(@@SPID as varchar(6)) order by logdate desc
END CATCH
PRINT @result

HTH

Answer 3

You can log the error details to a table. You could also create a log file, but that may require a CLR or xp_cmdshell to do. You can also send database mail, but that may cause spam issues and is not a proper log.

The table is simplest.

1. Create a table for storing the errors
2. Create a stored procedure that inserts into the error table
3. Call the stored procedure in the catch block

Look over Jeremy Kadlec's example provided in the link below:

http://www.mssqltips.com/sqlservertip/1152/standardized-sql-server-error-handling-and-centralized-logging/