Understanding How Restore and Recovery of Backups Work in SQL Server

 This topic is relevant for all SQL Server databases.

Restoring is the process of copying data from a backup and applying logged transactions to the data to roll it forward to the target recovery point. A data or differential backup contains sufficient transaction log records to allow rolling forward the active transactions as part of restoring each backup. Each backup also contain sufficient log to roll back uncommitted transactions to bring the database into a state that is transactionally consistent and usable. The process of rolling forward uncommitted transactions, if any, and bringing the database online is known as recovery.

The Roll Forward Set

The process of applying logged changes to data in a database to bring the data forward in time is known as rolling forward. The set of all data restored is called the roll forward set. A roll forward set is defined by restoring one or more full backups, such as a database or partial backup or a set of file backups. If a RESTORE statement specifies filegroups, files, or pages, only these items are included in the roll forward set. Otherwise, all files in the backup being restored are included in the roll forward set. If the full backup contains log records, the restored data will be rolled forward using this log.

Note

If you specify a filegroup during restore, the restore encompasses the whole filegroup as it currently exists. This includes any files added to the filegroup since the backup was taken. For more information, see Controlling Creation of Added Files During Roll Forward.

For differential backups, if any files were added to the database since the differential base, restoring a differential backup might overwrite pages in the roll forward set with data from the differential backup. For more information, see Using Differential Backups.

Restoring a differential backup updates a page only if the page is in the roll forward set, the page is contained in the backup, and the RESTORE statement either lists the page or its file or does not list any files or pages.

Under the full and bulk-logged recovery models, the log must be backed up separately. After you restore data and (optionally) differential backups, you would typically restore the subsequent log backups to bring the database up to the point of failure. Restoring a log backup rolls forward all pages in the roll forward set. For more information about log backups, see Working with Transaction Log Backups.

Restore Sequences

Each restore scenario is implemented by using one or more restore steps (operations), called a restore sequence. Each operation corresponds to an individual Transact-SQL RESTORE statement. A restore sequence moves affected data through one or more of the phases of restore.

For more information about Transact-SQL restore sequences and how to construct them, see Working with Restore Sequences for SQL Server Databases.

The Phases of Restore

A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

• The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.
• The redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point. At this point, a database typically has uncommitted transactions and is in an unusable state. In that case, an undo phase is required as part of recovering the database.
• The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored.

The rest of this section examines these phases in more detail.

Data Copy Phase

The first phase in any restore process is the data copy phase. The data copy phase initializes the contents of the database, files, or pages being restored. This phase is performed by restore database, restore file, and restore page operations using full or differential backups.

The data copy phase involves copying data from one or more full backups and, optionally, differential backups, and then resetting the contents of the affected database, files, or pages to the time that they were captured by those backups.

The oldest file or page in the roll forward set determines the starting point for the next phase: redo (roll forward).

Redo Phase (Roll Forward)

Redo (or roll forward) is the process of redoing logged changes to the data in the roll forward set to bring the data forward in time. To accomplish roll forward, the SQL Server Database Engine processes log backups as they are restored, starting with the log that is contained in full backups,

Restore avoids unnecessary roll forward. Generally, if data was read-only when it was backed up and has remained read-only, roll forward is unnecessary and is skipped.

The Recovery Point

The goal of roll forward is to return the data to its original state at the recovery point. The recovery point is the point to which the user specifies that the set of data be recovered. Under the full recovery model, you can specify the recovery point as a particular point in time, a marked transaction, or a log sequence number. Under the bulk-logged recovery model, you can restore to a point in time only if no bulk operations have been performed since the previous log backup.

Redo Consistency

In the redo phase, data is always rolled forward to a point that is redo consistent with the state of the database at the recovery point. All the data has been rolled forward to a point at which undo can occur.

The state of the database is defined by the primary file, as follows:

• If the primary file is being restored, the recovery point determines the state of the whole database. For example, if a database is being recovered to a point in time just before a table was accidentally dropped, the whole database must be restored to the same point in time.
• If the primary file is not being restored, the database state is known and restored data is rolled forward to a recovery point that is transactionally consistent with the database. SQL Server enforces this.

However, the database might contain changes made by transactions that are uncommitted at the recovery point. For online restore, data is recovered to a point in time consistent with the current state of the online part of the database.

A differential backup skips forward to when the differential backup was taken. Pages in the roll forward set are overwritten with any more recent ones from the differential backup.

Undo (Roll Back) Phase and Recovery

After the redo phase has rolled forward all the log transactions, a database typically contains changes made by transactions that are uncommitted at the recovery point. This makes the rolled forward data transactionally inconsistent. The recovery process opens the transaction log to identify uncommitted transactions. Uncommitted transactions are undone by being rolled back, unless they hold locks that prevent other transactions from viewing transactionally inconsistent data. This step, is called the undo (or roll back) phase. If the data is already transactionally consistent at the start of the recovery process, the undo phase is skipped. After the database is transactionally consistent, recovery brings the database online.

Note

In general terms, recovery is the set of operations that makes a database consistent at database startup. If the database was shut down regularly, recovery skips the redo and undo phases. This is known as restart recovery.

After one or more backups have been restored, recovery typically includes both the redo and undo phases. Every full and differential backup contains enough transaction log records to allow for the data in that backup to be recovered to a self-consistent state.

Note

During a crash recovery or a database mirroring failover, SQL Server 2005 Enterprise Edition lets users access the database during the undo phase. This is known as fast recovery. Fast recovery is possible because transactions that were uncommitted when the crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.

Relationship of RECOVERY and NORECOVERY Options to Restore Phases

A specific RESTORE statement either ends after the redo phase or continues through the undo phase, depending on whether the statement specified WITH NORECOVERY, as follows:

• WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.
  If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops.
  If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.
• WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by sing the RECOVERY option.
  For information about roll forward sets, see Understanding How Restore and Recovery of Backups Work in SQL Server.

Note

A third alternative, WITH STANDBY, is outside the scope of this topic.

For an in-depth description of these RESTORE options, see RESTORE (Transact-SQL).

Recovery Paths

A recovery path is a unique set of transformations that have evolved the database over time yet maintained its consistency. For information about recovery paths and the related concepts of recovery forks and recovery branches, see Recovery Paths.

Restoring a Database When SQL Server Is Offline

You can restore and recover a database by using SQL Writer while SQL Server is offline, if no full-text catalog is present. If a full-text catalog is associated with a database, you must first either start SQL Server or stop the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service.

When it is running, SQL Server forces full-text catalog files to close in preparation for the restore operation. However, if SQL Server is offline, MSFTESQL might keep certain full-text files open. This prevents the restore application from overwriting them. To force those full-text files to close, an application can shut down MSFTESQL.

To avoid having to do this, do either of the following:

• Start SQL Server. For more information, see Starting and Restarting Services).
• Stop MSFTESQL. For more information, see Stopping Services.

See Also

Concepts

Overview of Restore and Recovery in SQL Server
Working with Transaction Log Backups

Other Resources

Creating Full and Differential Backups of a SQL Server Database
Implementing Restore Scenarios for SQL Server Databases
Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Made minor updates in the following sections: "Restore Sequences" "Redo Consistency," formerly "Database Consistency" "Undo (Roll Back) Phase and Recovery," formerly "Backup Recovery" Incorporated and updated the former "Relationship of RESTORE Options to Restore Phases" topic and revised description of WITH RECOVERY.