Muokkaa

Jaa

Apply Transaction Log Backups (SQL Server)

  • Artikkeli

Applies to: SQL Server

The topic is relevant only for the full recovery model or bulk-logged recovery model.

This topic describes applying transaction log backups as part of restoring a SQL 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.

    Tip

    A best practice is to restore all the log backups (RESTORE LOG *database_name* WITH NORECOVERY). 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. 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 Server does not allow for the second transaction log backup to be applied after this point.

Note

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.

Time Event
8:00 A.M. Back up database to create a full database backup.
Noon Back up transaction log.
4:00 P.M. Back up transaction log.
6:00 P.M. Back up database to create a full database backup.
8:00 P.M. Back up transaction log.
9:45 P.M. Failure occurs.

For an explanation of this example sequence of backups, see Transaction Log Backups (SQL Server).

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:

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. Do not restore the 8:00 A.M. full database backup. Instead, restore the more recent 6:00 P.M. full database backup, and then apply the 8:00 P.M. log backup and the tail-log backup.

Alternative 2: Restore the database by using an earlier full database backup

This alternative process is useful if a problem prevents you from using the 6:00 P.M. full database backup. 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. Restore the 8:00 A.M. full database backup, and then restore all four transaction log backups in sequence. 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. For more information, Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Related tasks

To apply a transaction log backup

To restore to your recovery point

To recover a database after restoring backups using WITH NORECOVERY

See also

The Transaction Log (SQL Server)
SQL Server Transaction Log Architecture and Management Guide