How to process multiple error codes from a database restore using TRY...CATCH?

Question

So we have a homegrown hot-standby system (in case you're wondering why homegrown, it's because we've tried all the variations available from MS for SQL and TBH none have proven to meet our exacting standards) and we run the restore in a TRY...CATCH block but as a restore error tends to generate two codes, the first being the actual error, and the second being a generic "restore failed", the ERROR_NUMBER only returns the last code which is useless (which actually begs the question why does that error code exist anyway as it's completely useless and uninformative, and also why isn't there a more useful means to process error codes especially when there are multiple error codes generated). So how do we get to the meat of the matter, the first error code?

For example, and this is a common error we have encountered, in fact so far it's the only error we have encountered and we have adapted our code appropriately in order to avoid this but still the general principle applies:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 13714000038319300001, which is too early to apply to the database. A more recent log backup that includes LSN 13714000036214600001 can be restored.
Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

I have googled this and so far have not come across any suitable solution so decided to come here and ask the experts.

TIA

Michael MacGregor
Senior DBA

Answer 1

There are no good solution for this case, at least as long as you want to do it all inside SQL Server. But I discuss a number of solutions/kludges in chapter 7 in Part Three in my series on Error Handling and Transaction Handling in SQL Server: http://www.sommarskog.se/error_handling/Part3.html#AdminCommands.

Tip: The best solution is probably in section 7.5, rather than the CMD Catch Handler that I introduce myself.