tsql - 日志备份失败的捕获错误数

Question

当缺少完整备份时，以下代码将引发错误。

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.

我正在尝试使用以下代码捕获语句的错误号，但无法捕获错误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

输出:

  ERROR_NUMBER 3013

请有人可以帮助我捕获错误号4214

Answer 1

不知道错误号本身对您来说有多重要，但是您可以尝试执行以下操作:

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

如果该文件不存在，则ELSE语句会为您生成一个自定义错误。除了引发错误外，您还可以在此处执行其他操作。希望能有所帮助。