Catching multiple errors

The error values available as functions in the scope of CATCH block only return information about the last error raised, but sometimes (e.g. if a database restore fails because the disk is full) several errors are thrown.

Extended events can be used to trap all errors emerging from a SQL statement. This query thanks to TToni.

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