结尾日志备份 (SQL Server)Tail-Log Backups (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否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.

“结尾日志备份” 捕获尚未备份的任何日志记录(“结尾日志” ),以防丢失所做的工作并确保日志链完好无损。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
  • typetype
  • 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