Thursday, December 27, 2012

Understanding SQL Server restore phases

Restore means copying data from backup files. Using restore command you can restore part or whole database. The restore process contains multiple phases. So today I am going to explore all of them.

Data Copy Phase:
This is the first phase of restore process. In Data Copy Phase following operations are performed:
  1. In this phase all the data including index pages, log is being copied from the backup to database.
  2. It also initializes the contents of files and databases.
  3. This phase, restore data using full or differential backups.
  4. Restore file, restore database and restore page operations are used to perform restore in this phase.
Redo Phase: This phase is also known as Roll Forward. It will redo all the changes that occur before the crash so that database remains in same state as it was before the crash. In other words, this phase we try to bring database forward in time by applying logged changes. To perform this operation SQL Database Engine use full backup. Redo operation is performed only when it is required.

For example, if data remains read only throughout backup process then there is no need to perform it. The main motive of this phase is to bring the database back to its original state and this original state is also known as recovery point.

Undo Phase:
This phase is also known as Roll Back. The database becomes inconsistent after performing redo operation because it contains changes made by uncommitted transactions at the recovery point. So the recovery processes first identifies the uncommitted transaction and then undo them. The main goal of this phase is to maintain the integrity of database. The undo phase is skipped if database is already in consistent state.

Restore phase’s options: WITH RECOVERY or WITH NORECOVERY

WITH RECOVERY: The RESTORE statement with WITH RECOVERY option contains both undo and redo phase. This command is used to bring database in online state.

WITH NORECOVERY: Undo phase is skipped with WITH RECOVERY option to preserve those transactions that are not committed.

Fast Recovery Phase: This phase is available in SQL Server 2005 and later versions. The fast recovery allows user to access the database during the undo phase or we can say it make database available for user.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...