Capture Error number of log backup failure

Question

The below code throws an error when a full backup is missing.

BACKUP LOG [TESTDB] TO DISK = 'C:\DBADMIN\Backup\TESTDB_LOG.BAK'

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.

I am trying to capture the ERROR number of the statement with below code but couldn't capture the error 4214.

   BEGIN TRY 
      BACKUP LOG [TESTDB] TO DISK = 'C:\DBADMIN\Backup\TESTDB_LOG.BAK' 
   END TRY
   BEGIN CATCH
      SELECT ERROR_NUMBER() AS 'ERROR_NUMBER';
   END CATCH

Output :

  ERROR_NUMBER 3013

Please could someone help me to capture the error number 4214

Hi, welcome to StackOverflow. Looks to me as if the 2 error messages follow sequentially, the first effectively being purely a diagnostic, so that by the time you pick up the error_number() you are always going to get the 3013. Can't you just monitor for that one? — MandyShaw

rBalzer rBalzer · Accepted Answer · 2018-08-24T21:00:48

Not sure how important the error number itself is for you, but you could try something like:

DECLARE @inputFile NVARCHAR(100);
DECLARE @Exists int;

SET @inputFile = 'C:\DBADMIN\Backup\TESTDB_LOG.BAK' 
EXEC master.dbo.xp_fileexist @inputFile, @Exists OUTPUT

BEGIN TRY 
   IF @Exists = 1
      BACKUP LOG [TESTDB] TO DISK =  @inputFile
   ELSE
   EXEC sys.sp_addmessage @msgnum = 54214,@severity = 16,@msgtext  = N'BACKUP LOG cannot be performed because there is no current database backup.',@lang = 'us_english';   
     RAISERROR (54214,16,1)
   END TRY
   BEGIN CATCH
      SELECT ERROR_NUMBER() AS 'ERROR_NUMBER',  ERROR_MESSAGE() as 'ERROR_MESSAGE';
   END CATCH

The ELSE statement generates a custom error for you, if the file does not exist. You could do some different action here other than raising an error. Hope that helps.

Capture Error number of log backup failure

2 Answers