应用事务日志备份 (SQL Server)Apply Transaction 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

本主题只与完整恢复模式或大容量日志恢复模式相关。The topic is relevant only for the full recovery model or bulk-logged recovery model.

本主题介绍在还原 SQL ServerSQL Server 数据库过程中应用事务日志备份。This topic describes applying transaction log backups as part of restoring a SQL ServerSQL Server database.

还原事务日志备份的要求Requirements for restoring transaction log backups

若要应用事务日志备份,必须满足下列要求:To apply a transaction log backup, the following requirements must be met:

  • 为还原顺序准备足够的日志备份: 必须备份足够的日志记录,才能完成还原顺序。Enough Log Backups for a Restore Sequence : You must have enough log records backed up to complete a restore sequence. 必要的日志备份(需要时包含 结尾日志备份 )必须在还原顺序开始之前可用。The necessary log backups, including the tail-log backup where required, must be available before the start of the restore sequence.

  • 正确的还原顺序: 必须先还原上一个完整数据库备份或差异数据库备份。Correct restore order: The immediately previous full database backup or differential database backup must be restored first. 然后,在完整数据库备份或差异数据库备份后创建的所有事务日志必须按时间顺序还原。Then, all transaction logs that are created after that full or differential database backup must be restored in chronological order. 如果此事务日志链中的事务日志备份丢失或损坏,则您只能还原丢失的事务日志之前的事务日志。If a transaction log backup in this log chain is lost or damaged, you can restore only transaction logs before the missing transaction log.

  • 数据库尚未恢复: 除非已应用最后的事务日志,否则无法恢复数据库。Database not yet recovered: The database cannot be recovered until after the final transaction log has been applied. 如果要在还原其中一个中间事务日志备份之后恢复数据库,则在日志链结束之前,除非从完整数据库备份开始重新启动整个还原顺序,否则,将无法还原该点之前的数据库。If you recover the database after restoring one of the intermediate transaction log backups, that before the end of the log chain, you cannot restore the database past that point without restarting the complete restore sequence, starting with the full database backup.

    提示

    最佳方法是还原所有日志备份 (RESTORE LOG *database_name* WITH NORECOVERY)。A best practice is to restore all the log backups (RESTORE LOG *database_name* WITH NORECOVERY). 还原上一次日志备份后,用单独的操作恢复数据库 (RESTORE DATABASE *database_name* WITH RECOVERY)。Then, after restoring the last log backup, recover the database in a separate operation (RESTORE DATABASE *database_name* WITH RECOVERY).

恢复和事务日志Recovery and transaction logs

完成还原操作并恢复数据库后,将执行恢复过程,以确保数据库的完整性。When you finish the restore operation and recover the database, the recovery process is executed to ensure the integrity of the database. 有关恢复过程的详细信息,请参阅还原和恢复概述 (SQL Server)For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

恢复过程完成后,数据库将进入联机状态,不能再将其他事务日志备份应用到数据库。After the recovery process completes, the database goes online, and no more transaction log backups can be applied to the database. 例如,一系列事务日志备份包含一个运行时间长的事务。For example, a series of transaction log backups contain a long-running transaction. 该事务的起点记录在第一个事务日志备份中,终点记录在第二个事务日志备份中。The start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. 第一个事务日志备份中没有任何关于提交或回滚操作的记录。There is no record of a commit or rollback operation in the first transaction log backup. 如果在应用第一个事务日志备份后运行恢复操作,则运行时间长的事务被视为未完成,并且将回滚事务的第一个事务日志备份中记录的数据修改。If a recovery operation runs when the first transaction log backup is applied, the long-running transaction is treated as incomplete, and data modifications recorded in the first transaction log backup for the transaction are rolled back. SQL ServerSQL Server 不允许在此点后应用第二个事务日志备份。does not allow for the second transaction log backup to be applied after this point.

备注

某些情况下可以在日志还原期间显式添加文件。In some circumstances, you can explicitly add a file during log restore.

使用日志备份来还原到故障点Use log backups to restore to the failure point

假设有下列事件顺序。Assume the following sequence of events.

TimeTime 事件Event
上午 8:008:00 A.M. 备份数据库以创建完整数据库备份。Back up database to create a full database backup.
中午Noon 备份事务日志。Back up transaction log.
下午 4:004:00 P.M. 备份事务日志。Back up transaction log.
下午 6:006:00 P.M. 备份数据库以创建完整数据库备份。Back up database to create a full database backup.
晚上 8:008:00 P.M. 备份事务日志。Back up transaction log.
晚上 9:459:45 P.M. 出现故障。Failure occurs.

有关此示例备份顺序的说明,请参阅事务日志备份 (SQL Server)For an explanation of this example sequence of backups, see Transaction Log Backups (SQL Server).

若要将数据库还原到晚上 9:45(故障点)时的状态,To restore the database to its state at 9:45 P.M. 可以使用以下两种备选过程:(the point of failure), either of the following alternative procedures can be used:

替换选项 1:使用最新的完整数据库备份还原数据库Alternative 1: Restore the database by using the most recent full database backup

  1. 失败时创建当前活动事务日志的结尾日志备份。Create a tail-log backup of the currently active transaction log as of the point of failure.

  2. 不要还原上午 8:00 的Do not restore the 8:00 A.M. 所需的时间长。full database backup. 相反,应还原下午 6:00 的Instead, restore the more recent 6:00 P.M. 这一时间更近的完整数据库备份,然后应用晚上 8:00 的full database backup, and then apply the 8:00 P.M. 日志备份和结尾日志备份。log backup and the tail-log backup.

替换选项 2:使用较早的完整数据库备份还原数据库Alternative 2: Restore the database by using an earlier full database backup

如果出现问题,使您无法使用下午 6:00 的完整数据库备份,This alternative process is useful if a problem prevents you from using the 6:00 P.M. 所需的时间长。full database backup. 此过程比从下午 6:00 的完整数据库备份还原This process takes longer than restoring from the 6:00 P.M. 所需的时间长。full database backup.

  1. 失败时创建当前活动事务日志的结尾日志备份。Create a tail-log backup of the currently active transaction log as of the point of failure.

  2. 还原上午 8:00 的Restore the 8:00 A.M. 完整数据库备份,然后按顺序还原所有四个事务日志备份。full database backup, and then restore all four transaction log backups in sequence. 所有完成的事务都将前滚到晚上 9:45。This rolls forward all completed transactions up to 9:45 P.M.

    此备选过程指出了冗余安全性,该安全性通过维护一系列完整数据库备份中的事务日志链备份来获得。This alternative points out the redundant security offered by maintaining a chain of transaction log backups across a series of full database backups.

某些情况下,您还可以使用事务日志将数据库还原到特定的时间点。In some cases, you can also use transaction logs to restore a database to a specific point in time. 有关详细信息,请参阅 将 SQL Server 数据库还原到某个时间点(完整恢复模式)For more information, Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Related tasksRelated tasks

应用事务日志备份To apply a transaction log backup

还原到恢复点To restore to your recovery point

使用 WITH NORECOVERY 在还原备份后恢复数据库To recover a database after restoring backups using WITH NORECOVERY

另请参阅See also

事务日志 (SQL Server) The Transaction Log (SQL Server)
SQL Server 事务日志体系结构和管理指南SQL Server Transaction Log Architecture and Management Guide