Recovery Paths

Understanding recovery paths is important if you use differential or log backups, and you recover a database to a previous point in time by either of the following methods:

• Performing a point-in-time restore
• Performing a recovery without first restoring all of the log backups or most recent differential backup.

If you recover a database to an earlier recovery point and start using the database from that point, a new recovery path originates. The recovery path is the sequence of data and log backups that have brought a database to a particular point in time, either by regular database use or by a specific data and log restore. A recovery path consists of a unique set of specific transformations that have evolved the database over time, yet maintained the consistency of the database. The following illustration shows the relationship between a recovery point and the resulting recovery paths.

In general, a recovery point starts a new recovery path because transactions have to be rolled back and the database is now in a unique state. Preexisting backups may now have log sequence numbers (LSNs) greater than the LSN of this recovery point. The LSNs in these backups exist on a different recovery branch from the new branch that is created by the current recovery operation.

Best practice To avoid creating a recovery path that has multiple recovery forks, perform a complete set of data backups as soon as possible after you recover the database. This approach guarantees that all backups are taken on a single recovery branch. To verify this, you can look at the last_recovery_fork_guid column in the backupset table or RESTORE HEADERONLY results set.

The following situations create a new recovery path because the database is not restored to the "end of time". Thereafter, backups exist that can take the database down two or more recovery paths, all of which use the same range of LSNs.

• Restoring a full database backup and a differential database backup and recovering the database without applying existing transaction log backups.
• Recovering the database at the end of a differential backup other than the most recent differential backup.
• Recovering the database at the end of a transaction log backup other than the most recent transaction log backup.
• Recovering the database at a specific time or a marked transaction within a transaction log backup.

Example of a Recovery Path

The following illustration shows the forking of a new recovery path when the database is recovered. In this illustration, a full database backup and a sequence of four log backups are created. The database is then restored to the end of Log Backup 2 by restoring the full database backup, Log Backup 1, and Log Backup 2. The database is recovered at this point, creating a new recovery fork. The database is then used for a time, and two more transaction log backups, Log Backup 5 and Log Backup 6, are created.

The database backup and the first four log backups are all on branch 1. Log Backup 5 and Log Backup 6 are on branch 2. The recovery fork contains the last LSN of Log Backup 2 (Log_Backup_2.LastLSN) and the first LSN of Log Backup 5 (Log_Backup_5.FirstLSN).

Inside Log Backup 5, the first_recovery_fork_guid identifies branch 1, and last_recovery_fork_guid identifies branch 2. The recovery path is branch 1, branch 2.

To Restore and Roll Forward Along an Old Path

We recommend that you avoid using an old recovery path. This is because using an old recovery causes the database to have committed transactions in two different time intervals. However, if necessary, you can roll forward along an old recovery path by following the sequence of backups taken before the creation of the current recovery path. For example, you can use backups taken before a point-in-time recovery to reach points along the old path.

For example, based on the backups created in the previous illustration, after Log Backup 5 and Log Backup 6 were created, it is still possible to restore to the end of Log Backup 3, which is on the old recovery path.

To Restore and Roll Forward from an Old Path to a New Path

The SQL Server Database Engine prevents a single restore sequence from by using backups that do not go together, that is, that try to roll forward along different recovery paths. This restriction maintains the consistency of a database after a recovery.

To restore and roll forward along a new recovery path, construct distinct restore sequences for the backups before the recovery point and for the backups after the recovery point:

1. Restore the backups taken were before the recovery that introduced the new recovery path. Exclude the backup that contains the recovery point.
2. Roll forward along the new recovery path by restoring the backups that have been taken since the recovery path was created.

For example, based on the backups that are created in the previous illustration, assume that Log Backup 3 covers the time from 10 AM to 11 AM. A point-in-time restore has been performed that specified STOPAT**=10:**30. This forked the recovery path and started a new recovery branch. Branch 2. The first log backup on the new branch, Log Backup 5, contains the same first LSN as Log Backup 3, replacing Log Backup 3, which is now defunct. To restore backups on the new recovery path (starting on branch 1 and ending on branch 2), the restore sequence is: Full Database Backup, Log Backup 1, Log Backup 2, Log Backup 5, and Log Backup 6.

Managing Recovery Forks

A recovery branch is a range of LSNs that share the same GUID. A recovery path describes a range of LSNs from a start point (LSN,GUID) to an end point (LSN,GUID). The range of LSNs in a recovery path may traverse one or more recovery branches from start to end. A new recovery branch originates when a database is created and when RESTORE WITH RECOVERY generates a recovery fork.

A recovery fork is the point (LSN,GUID) at which a new recovery branch is started, every time a RESTORE WITH RECOVERY is performed. Each recovery fork determines a parent-child relationship between the recovery branches.

Recovering the database sets the whole database state, including the next LSN, to the recovery point. LSNs are then reused, starting with the fork_point_lsn. When constructing a restore sequence, therefore, backups must be linked by recovery fork and also by LSN, because the same LSN might exist on more than one fork. The following illustration shows LSN reuse. It shows how LSNs are reused in different recovery forks.

If a restore sequence must incorporate backups that traverse a recovery fork, the restore sequence must be constructed so that the backups that are used follow the correct recovery path to the recovery point. Backups include backupset.first_recovery_fork_guid and backupset.last_recovery_fork_guid for this purpose. These are used to link the backups to make sure the sequence follows the correct fork.

The values in the backupset history table let you to determine which backup set to use:

• For each log backup in the sequence to be restored, first_recovery_fork_guid must equal last_recovery_fork_guid of the prior backup in the sequence.
  
• Data and differential backups must also be linked.
  If the log backup contains both the last LSN of a full database backup or a differential database backup and a fork point, the linking test depends on the location of the last LSN relative to the fork point.
  The linking tests are as follows, using values from backupset:
  • If last_lsn is less than or equal to fork_point_lsn, the last_recovery_fork_guid of the data or differential backup must equal the first_recovery_fork_guid of the log backup. The following illustration shows a case in which last_lsn is less than fork_point_lsn.
    
  • If last_lsn is greater than fork_point_lsn, the last_recovery_fork_guid of the data or differential backup must equal the last_recovery_fork_guid of the log backup. The following illlustration shows a case in which last_lsn is greater than fork_point_lsn.
    
• For a differential backup, locate the differential base by using backupset.differential_base_guid.
  If the differential is multibased, backupset.differential_base_guid is NULL, and you must determine the differential bases file-by-file by using backupfile.differential_base_guid.

See Also

Concepts

Copying Databases with Backup and Restore
Planning and Performing Restore Sequences (Full Recovery Model)
Introduction to Log Sequence Numbers
Log Sequence Numbers and Restore Planning
Base of a Differential Backup

Other Resources

Implementing Restore Scenarios for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance