During the process of restoring SQL Server databases, data is copied from a backup and changes are then made to this data by applying logged transactions. Backups comprise transaction log records that can be used to roll forward any active transactions or roll back any uncommitted transactions to maintain transactional consistency in the database.
During the process of restoring SQL Server databases, data is copied from a backup and changes are then made to this data by applying logged transactions. Backups comprise transaction log records that can be used to roll forward any active transactions or roll back any uncommitted transactions to maintain transactional consistency in the database. When you apply changes to data to roll it forward to a predefined recovery point, the process is termed as recovery. The data to which you apply the logged changes is called the roll forward set.
In general, a database restore process includes three phases: Data Copy, Redo (roll forward), and Undo (roll back). Mentioned below is the detailed course of action followed in each of these phases:
I. Data Copy Phase
In the first phase, everything from the backup media (data, log, and index pages) is copied to the database files. This phase also initializes the data contained in the database, files, or pages to be restored. It usually involves restore operations using full or differential backups. When all the data in the affected database, files, or pages is reset, the next phase is initiated.
II. Redo Phase (Roll Forward)
The Redo phase starts where the Data Copy phase ends. This phase involves redoing changes to the data to bring it forward in time. The SQL Server Database Engine starts reading log backups and the log contained in full backups is the first to be processed. A roll forward won't take place if the data at the time of performing backups was read-only.
You have the choice to specify a recovery point to which you want the set of data to be recovered. If you are using SQL Server full recovery model, the recovery point can be any specific point in time, a transaction, or a log sequence number. For bulk-logged recovery model, you can specify any point in time as your recovery point until there are no bulk operations being performed.
The primary goal of this phase is to make the data redo consistent with the state of the database. When you are restoring your primary file, the database state can be assumed to be the one that exists at the target recovery point. Otherwise, the database state is known beforehand.
If there are uncommitted transactions in your database at the recovery point, then data is roll forward to a point in time that is redo consistent with the current state of your online database.
III. Undo (Roll Back) Phase
Once the Redo phase is finished, what is left is only the uncommitted transactions at the recovery point. Until these changes are not applied, your rolled forward data will remain transactionally inconsistent. The database engine reads all uncommitted transactions in the log and rolls back them. This process is known as 'Undo'.
SQL Server 2005 Enterprise Edition and all the later versions include a feature known as 'fast recovery' to allow the users gain access to the database immediately after completion of the Redo phase. This is easily accomplished using a mechanism called 'lock logging'. With SQL Server 2005, the log records also contain information about any locks that are held by the transaction at the time it is running. In case a crash occurs or during a database mirroring failover, the transaction is left uncommitted. This transaction reacquires those locks that were held by it previously when the Undo phase starts. These locks protect the changes made by transactions when they are rolled back and allow the users to access the database without interruption.
About the Author