SQL Server's BACKUP DATABASE - how to access the original exception?

Question

DECLARE @DbName SYSNAME;
SET @DbName = 'MyDatabase';

BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT, 
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

This, sure enough, is a problematic code, given you're running under NETWORK SERVICE privileges. The output I am getting from it (because I am trying to write to c:\ to which NETWORK SERVICE doesn't have write access) is following when running in SSMS:

Msg 3201, Level 16, State 1, Line 4
Cannot open backup device 'C:\MyDatabase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

Both surface in SSMS's Query Analyzer output as exceptions, but it looks like (maybe I'm talking gibberish here) that the first filesystem-related one is handled internally by the BACKUP statement which then replaces the message with a more generic "backup is terminating abnormally".

If I then try to handle the exceptions in my script like so

BEGIN TRY

  DECLARE @DbName SYSNAME;
  SET @DbName = 'MyDatabase';

  BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT, 
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

END TRY
BEGIN CATCH
  SELECT  ERROR_NUMBER()
        , ERROR_MESSAGE()
END CATCH

then I am only intercepting (well, in this case only showing with SELECT) the 3013 exception, obviously.

Question: is it at all possible to programmatically access the first exception (as it makes far better sense for logging problems during the script execution)?

Thank you!

Answer 1

You can use extended events to capture all errors in your session (this will include some internal errors in your case that the user doesn't get to see normally).

Use the following code to do this (SQL Server 2008 minimum)

CREATE EVENT SESSION ErrorCatcher ON SERVER
ADD EVENT sqlserver.error_reported (WHERE severity >= 16)
ADD TARGET package0.ring_buffer
WITH (
     EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
     )

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START

BEGIN TRY
    BACKUP DATABASE MyDatabase
        TO DISK = 'C:\MyDatabase.bak'
        WITH INIT, 
        NAME = 'MyDatabase-FullBackup',
        STATS = 5;
END TRY
BEGIN CATCH
    DECLARE @target_data XML

    WAITFOR DELAY '00:00:01' -- Wait for the MAX_DISPATCH_LATENCY time

    SELECT @target_data = CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'ErrorCatcher'

    SELECT 
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
END CATCH    

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = STOP

DROP EVENT SESSION ErrorCatcher ON SERVER

Of course you can create the event session only once and just start/stop it on demand. The session is persistent and remains on the server until you drop it.

The ring buffer target is resident in memory and with default settings uses up to 4 MB to capture events before it starts deleting old events (on a FIFO basis). The buffer is also released when you stop the session.