Applying Transaction Log Backups

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.

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.

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.

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

Create a tail-log backup of the currently active transaction log as of the point of failure.

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.

Create a tail-log backup of the currently active transaction log as of the point of failure.

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.