question

VinceThomas avatar image
VinceThomas asked

I am unable to catch all errors in SQL server

Hi All. while taking log backup of a database before taking full backup I am getting the below errors : Msg 4214, Level 16, State 1, Line 4 BACKUP LOG cannot be performed because there is no current database backup. Msg 3013, Level 16, State 1, Line 4 BACKUP LOG is terminating abnormally. But while using Try Catch method I am unable to catch 4214 error:

Using Powershell ISE I am able to do it.

Could somebody please help me in writing a stored procedure to catch the 4214 error too.

thank you

t-sqlerror-messageerror handling
try-catch.png (15.8 KiB)
powershell.png (29.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

TRY..CATCH only picks up the last error.

If you're using SQL2012 or later, you could use THROW within the CATCH statement. This will return all the errors, but it'll also abort processing, so you might not be able to do anything useful within the scope of the same script...

Erland Sommarskog has, in one of his epic essays, some other thoughts. Check it out - http://www.sommarskog.se/error_handling/Part3.html#AdminCommands

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

VinceThomas avatar image
VinceThomas answered

Hi @ThomasRushton how about the use of raise error with cursor. Will it be of useful in thus case.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Possibly not. See the description of processing of RAISERROR at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017#remarks to figure out how best to fit it with your code.

You might want to just run a query against msdb.dbo.backupset to see if a full backup has been taken before attempting to take your TLog backup. https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.