• Article

Planning and Performing Restore Sequences (Full Recovery Model)

 This topic is relevant for SQL Server databases that ordinarily use the full recovery model.

A restore sequence is a sequence of one or more RESTORE statements. Typically, a restore sequences initializes the contents of the database, files, and/or pages being restored (the data-copy phase), rolls forward logged transactions (the redo phase), and rolls back uncommitted transactions (the undo phase). For more information about these phases, see Understanding How Restore and Recovery of Backups Work in SQL Server.

Note

For an introduction to backup types, see Backup Overview (SQL Server).

In simple cases, a restore sequence requires only a full database backup, a differential database backup, and the subsequent log backups. In these cases, constructing a correct restore sequence is easy. For example, to restore a whole database to the point of a failure, start by backing up the active transaction log (the tail of the log). Then, restore the most recent full database backup, the most recent differential backup (if any), and all subsequent log backups in the order in which they were taken.

In more complex cases, constructing a correct restore sequence can be a complex process. For example, a restore sequence might require multiple file backups or restoring data to a specific point in time. In very complex cases, you might even have to traverse a forked recovery path that spans one or more recovery forks.

Planning a Restore Sequence

Before you start a restore sequence, follow these steps:

  1. Create a tail-log backup of the database, if you can. For more information, see Tail-Log Backups.

  2. Determine the target recovery point.

    The target recovery point can be any point in time or mark within a transaction log backup. For more information, see Restoring a Database to a Point Within a Backup or Using Marked Transactions (Full Recovery Model).

  3. Determine the type of restore you want to perform. For more information, see the following topics:

  4. Identify which backups you require and make sure that the necessary media sets and backup devices are available. For more information, see Working with Backup Media in SQL Server.

Performing a Restore Sequence

To perform a restore sequence, follow these steps:

  1. To start the sequence, restore a one or more data backups, such as: a database backup, a partial backup, one or more file backups.

  2. Optionally, restore the latest differential backups that are based on these full backups.

    For each full backup that you plan to restore, determine whether it is the base for any differential backups. If so, restore most recent differential backup, if you can. For more information, see Using Differential Backups.

  3. Roll forward the database by restoring log backups in sequence, finishing with the backup that contains the recovery point. Whether you have to apply all the log backups depends on what log backup contains the target recovery point, as follows:

    • If the recovery point is the point of a failure, you must restore every log backup that was created since the last data (full or differential) backup you restored. For more information, see Applying Transaction Log Backups.

    • For a point-in-time restore, you might not require the most recent log backups. For more information, see Point in Time Restore.

Restarting a Restore Sequence

If you encounter a problem with the outcome of a restore sequence, you can quit it and restart the restore sequence over from the start. For example, if you accidentally restore too many log backups and overshoot the intended recovery point, you must restart the restore sequence up to log backup that contains the target recovery point.