Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

Plan and Perform Restore Sequences (Full Recovery Model)

This topic explains how to plan and perform a restore sequence for a SQL Server databases that ordinarily uses 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).

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.

Note Note

A recovery path is the sequence of data and log backups that have brought a database to a particular point in time (known as a recovery point). A recovery path is a specific set of transformations that have evolved the database over time, yet have maintained the consistency of the database. 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 can traverse one or more recovery branches from start to end.

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 (SQL Server).

  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 Restore a SQL Server Database to a Point in Time (Full Recovery Model) or Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

  3. Determine the type of restore you want to perform. For more information, see Restore and Recovery Overview (SQL Server).

  4. Identify which backups you require and make sure that the necessary media sets and backup devices are available. For more information, see Backup Devices (SQL Server) and Media Sets, Media Families, and Backup Sets (SQL Server).

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 Differential Backups (SQL Server).

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.