Share via

Applying Transaction Log Backups

  • Article

Icon showing a blue database disk 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. To apply a transaction log backup, the following requirements must be met:

  • The immediately previous full database backup or differential database backup must be restored first.
  • 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.
  • The database has not yet been 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.

Recovery and Transaction Logs

When you finish the restore operation and recover the database, recovery rolls back all incomplete transactions. This is known as the undo phase. Rolling back is required to restore the integrity of the database. After rollback, 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. For more information, see Controlling Creation of Added Files During Roll Forward.

Having 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.

Using Log Backups to Restore to the Point of Failure

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.

Note

For an explanation of this example sequence of backups, see Creating Transaction Log Backups.

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.
    This occurs again for the 9:45 P.M. transaction tail-log backup.

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

Note

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.

Using Log Backups to Restore to a Specific Point in Time

In some cases, you can also use transaction logs to restore a database to a specific point in time. For more information, see Restoring a Database to a Point Within a Backup.

Appling Transaction Log Backups

A best practice is to restore all the log backups by using WITH NORECOVERY:

RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY

Then, after restoring the last log backup, recover the database in a separate operation:

RESTORE DATABASE database_name WITH RECOVERY

To apply a transaction log backup

To restore to your recovery point

See Also

Other Resources

Understanding and Managing Transaction Logs

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Incorporated the offline restore section of the former "Having Enough Log Backups for a Restore Sequence" topic.