A Closer Look at the Restore and Recovery Process in SQL Server

Adamgorge
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 6493 red flag

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. 


Recovery Point


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. 


Redo Consistency


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


Fast Recovery


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.

Page copy protected against web site content infringement by Copyscape

About the Author

Adamgorge
Full Name: Adam Gorge
Member Level: Starter
Member Status: Member
Member Since: 3/20/2013 11:56:27 PM
Country: United States

http://www.stellarinfo.com/sql-recovery.htm
Adam Gorge is an expert handler of SQL Database errors, SQL database corruption issues etc. He has written many articles on SQL database, its inbuilt utilities, SQL database free tools & sql database recovery software etc.

Login to vote for this post.

Comments or Responses

Posted by: Elliswhite on: 9/8/2014 | Points: 25
Hi guys

According to many SQL users the best way to recover SQL database is SQL database recovery software which a time saving and efficient recovery option. By using this tool you can export recovered SQL database directly to the SQL Server without any extra effort. Read more and try it from here:- http://www.sqlrecoverysoftware.net/sql-server-mdf-file/


Posted by: Jonathanstorms on: 8/6/2015 | Points: 25
If you want to recover corrupt SQL database then you can use SQL database recovery software which is relevant solutions to fix database from any kind of corruption & recover SQL MDF file along with entire database like tables, views, triggers etc. Read more :- http://www.recoverfilesdownload.com/sql-database-recovery.html

Login to post response

Comment using Facebook(Author doesn't get notification)