Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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!

share|improve this question
    
This question belongs on Database Administrators –  Kermit Nov 25 '13 at 14:48
    
@marc_s, thanks - although I am completely in the context of tsql, i.e. I need the calling script to see the more complete exception "trace" and not some client app on the other side of db library. –  Yuri Makassiouk Nov 25 '13 at 15:14
    
Sorry - missed that point (catching exception in T-SQL). –  marc_s Nov 25 '13 at 15:19
    
@FreshPrinceOfSO, well, the question is more about handling the exception than about the backup command itself. So I'd say it's more a T-SQL development issue than SA. –  Yuri Makassiouk Nov 25 '13 at 15:32
    
@YuriMakassiouk Backups deal with DBA responsibilities. I think it's a decent fit. –  Kermit Nov 25 '13 at 15:41
show 5 more comments

migrated from stackoverflow.com Nov 26 '13 at 12:20

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.