結尾記錄備份 (SQL Server)Tail-Log Backups (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主題僅與使用完整或大量記錄復原模式備份和還原 SQL ServerSQL Server 資料庫有關。This topic is relevant only for backup and restore of SQL ServerSQL Server databases that are using the full or bulk-logged recovery models.

「結尾記錄備份」 (tail-log backup) 可擷取任何尚未備份的記錄檔記錄 (「記錄結尾」 (tail of the log)),來防止工作遺失,並保持記錄鏈結完整。A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. SQL ServerSQL Server 資料庫復原到最新時間點之前,必須備份其交易記錄的結尾。Before you can recover a SQL ServerSQL Server database to its latest point in time, you must back up the tail of its transaction log. 結尾記錄備份會是資料庫之復原計畫中感興趣的最後一個備份。The tail-log backup will be the last backup of interest in the recovery plan for the database.

注意: 並不是所有的還原實例都需要結尾記錄備份。NOTE: Not all restore scenarios require a tail-log backup. 如果復原點是包含在較早的記錄備份中,則不需要結尾記錄備份。You do not need a tail-log backup if the recovery point is contained in an earlier log backup. 而且,如果您要移動或取代 (覆寫) 資料庫,而且不需要將它還原至最近備份之後的某個時間點,就不需要有結尾記錄備份。Also, a tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup.

需要結尾記錄備份的實例Scenarios That Require a Tail-Log Backup

建議您在下列實例中進行結尾記錄備份:We recommend that you take a tail-log backup in the following scenarios:

  • 如果資料庫在線上,而且您打算執行資料庫的還原作業,請從備份記錄結尾開始。If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. 若要避免線上資料庫的錯誤,則必須使用... BACKUP Transact-SQLTransact-SQL 陳述式的 WITH NORECOVERY 選項。To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQLTransact-SQL statement.

  • 如果資料庫離線且無法啟動,並且需要還原資料庫,請先備份記錄結尾。If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. 因為這段時間不會發生交易,所以使用 WITH NORECOVERY 是選擇性的。Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

  • 如果資料庫損毀,請嘗試使用 BACKUP 陳述式的 WITH CONTINUE_AFTER_ERROR 選項來進行結尾記錄備份。If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

    在損毀的資料庫上,只有在記錄檔未損壞、資料庫處於支援結尾記錄備份的狀態,以及資料庫未包含任何大量記錄的變更時,記錄結尾的備份才會成功。On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes. 如果無法建立結尾記錄備份,則會遺失在最新記錄備份之後確定的任何交易。If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.

下表摘要說明 BACKUP NORECOVERY 和 CONTINUE_AFTER_ERROR 選項。The following table summarizes the BACKUP NORECOVERY and CONTINUE_AFTER_ERROR options.

BACKUP LOG 選項BACKUP LOG option 註解Comments
NORECOVERYNORECOVERY 每當您打算在資料庫上繼續還原作業時,請使用 NORECOVERY。Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY 會讓資料庫進入還原狀態。NORECOVERY takes the database into the restoring state. 這樣可以保證資料庫不會在結尾記錄備份之後變更。This guarantees that the database does not change after the tail-log backup. 除非也指定了 NO_TRUNCATE 選項或 COPY_ONLY 選項,否則將會截斷記錄。The log will be truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified.

重要: 除非資料庫受損,否則請避免使用 NO_TRUNCATE。Important: Avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERRORCONTINUE_AFTER_ERROR 只有在您要備份受損資料庫的結尾時,才使用 CONTINUE_AFTER_ERROR。Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.

當您在受損資料庫上使用結尾記錄備份時,一般可在記錄備份中擷取到的某些中繼資料可能無法使用。When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. 如需詳細資訊,請參閱本主題中的 具有不完整備份中繼資料的結尾記錄備份For more information, see Tail-Log Backups That Have Incomplete Backup Metadata, in this topic.

具有不完整備份中繼資料的結尾記錄備份Tail-Log backups that have incomplete backup metadata

即使資料庫離線、損毀或遺漏資料檔案,結尾記錄備份還是會擷取記錄檔的結尾。Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. 這可能會導致還原資訊命令和 msdb產生不完整的中繼資料。This might cause incomplete metadata from the restore information commands and msdb. 不過,只有中繼資料不完整,所擷取的記錄仍然完整可用。However, only the metadata is incomplete; the captured log is complete and usable.

如果結尾記錄備份具有不完整的中繼資料, backupset 資料表中的 has_incomplete_metadata 會設為 1If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. 此外,在 RESTORE HEADERONLY的輸出中, HasIncompleteMetadata 也會設為 1Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1.

如果結尾記錄備份的中繼資料不完整, backupfilegroup 資料表將會遺失有關檔案群組在結尾記錄備份期間的大部分資訊。If the metadata in a tail-log backup is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. 大部分 backupfilegroup 資料表資料行為 NULL,只有下列資料行具有意義:Most of the backupfilegroup table columns are NULL; the only meaningful columns are as follows:

  • backup_set_idbackup_set_id
  • filegroup_idfilegroup_id
  • 型別type
  • type_desctype_desc
  • is_readonlyis_readonly

相關工作Related Tasks

若要建立結尾記錄備份,請參閱資料庫損毀時備份交易記錄 (SQL Server)To create a tail-log backup, see Back Up the Transaction Log When the Database Is Damaged (SQL Server).

若要還原交易記錄備份,請參閱還原交易記錄備份 (SQL Server)To restore a transaction log backup, see Restore a Transaction Log Backup (SQL Server).

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
只複製備份 (SQL Server) Copy-Only Backups (SQL Server)
交易記錄備份 (SQL Server) Transaction Log Backups (SQL Server)
套用交易記錄備份 (SQL Server) Apply Transaction Log Backups (SQL Server)
SQL Server 交易記錄架構與管理指南SQL Server Transaction Log Architecture and Management Guide