修正SQL Server事務日志備份/截斷的JOB錯誤

目錄

• • 目錄
  • 問題描述
  • 問題分析
  • 解決辦法
  • 參考資料

問題描述

通過SSMS中的維護計劃配置了事務日志（Transaction Log）備份的Job，但測試執行後出現如下報錯資訊：

Date        2/4/2016 8:00:00 AM
Log     Job History (backup_log.sub)

Step ID     1
Server      MAXIMO75
Job Name        backup_log.sub
Step Name       sub
Duration        00:00:02
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: WORKGROUP\MAXIMO75$. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  8:00:00 AM  Progress: 2016-02-04 08:00:01.74     Source: {3EC7849D-C143-4091-99DB-8AADBABF5164}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: -- ::     Source: Maintenance Cleanup Task      Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\backup\lo...".: % complete  End Progress  Error: -- ::     Code:      Source: Back Up Database Task Execute SQL Task     Description: Executing the query "BACKUP LOG [maxdb75] TO  DISK = N'D:\backup\log\ma..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.  BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE ().  Started:  :: AM  Finished: :: AM  Elapsed:   seconds.  The package execution failed.  The step failed.
           

問題分析

仔細檢視上述報錯資訊中的提示：

“BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

即中文如下：

無法執行 BACKUP LOG，因為目前沒有資料庫備份。 BACKUP LOG 正在異常終止。”。失敗的原因可能有: 查詢本身有問題、未正确設定 “ResultSet” 屬性、未正确設定參數或未正确建立連接配接。

這是由于此DB庫從未執行過資料庫完整備份或剛将資料庫的日志級别從簡單模式切換到完整模式。

見MSDN中（Working with Transaction Log Backups）的描述

Before you can create the first log backup, you must create a full backup

解決辦法

執行一次完整的資料備份，然後再執行事務日志備份的Job

參考資料

1. 使用事務日志備份(Working with Transaction Log Backups)

   https://msdn.microsoft.com/zh-cn/library/ms190440.aspx