Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Difference between restore RECOVERY and NORECOVERY

07 January,2013 by Jack Vamvas

In the SQL Server Database RESTORE there is an option to restore with either a RECOVERY or NORECOVERY option. Understanding the difference and knowing when to apply the options is useful.

When the RECOVERY option is used   such as :

 

RESTORE DATABASE MyDB WITH RECOVERY;

 

The RECOVERY command requests the current RESTORE to roll back all uncommitted transactions.  When all uncommitted transactions are rolled back the database is placed into a ready state. The database is ready to use.

A criteria of  database recovery is to ensure all data sets  restored are consistent with the database. Errors are reported if the roll forward data set is not database consistent.

When the NORECOVERY option is used such as:

 

RESTORE DATABASE MyDB WITH NORECOVERY;

 

The NORECOVERY command requests that no uncommitted transactions are rolled back.  As a result, roll forward can continue with the next statement in the RESTORE sequence.  

 Typically the NORECOVERY is used in situations :

 a)       A further transaction log file has to be applied. For example,  a failure may occur and a RESTORE is required form the last known good LOG BACKUP. The situation may require:

 

--–  Restore last full backup but don’t recover
RESTORE DATABASE myDB    WITH NORECOVERY;
--- Restore Logs , repeat as often as required
RESTORE LOG myDBLog  WITH NORECOVERY;
           ---Database recovery 
RESTORE DATABASE MyDB WITH RECOVERY;


 b)  A DIFFERENTIAL file has to be applied

 

 --–  Restore last full backup but don’t recover
RESTORE DATABASE myDB    WITH NORECOVERY;
--- Restore differential
 RESTORE DATABASE myDBLog  WITH NORECOVERY;
           ---Database recovery 
RESTORE DATABASE MyDB WITH RECOVERY;

 Read More

SQL Server - RESTORE VERIFYONLY - SQL Server DBA

Differential backup cannot be restored . BACKUP ... - SQL Server DBA

SQL Server faster restores with instant file initialisation


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer