SQL with Manoj

–> Question:

I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.

Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

My questions are:

1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?
 

–> Answer:

While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.

The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows. 

Ref link.