SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

July 15, 2009 by pinaldave

I maintain a spreadsheet of questions sent by users and from that I single out a topic to write and share my knowledge and opinion. Unless and until I find an issue appealing, I do not prefer to write about it, till the issue crosses the threshold. Today the question that crossed the threshold is – what is the difference between NORECOERY and RECOVERY when restoring database.

The fact is that one can easily go to MSDN and learn the difference, so I head myself to MSDN and read the difference. This is what the Book On Line suggests here.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

Book On Line has very clearly explained the same subject and provides succinct explanation of the difference.

In other words -

While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep database in state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create itegrity issues. Once all backup file is restored run RESTORE command with RECOVERY option to get database online and operational.

It is also important to be acquainted with the restore sequence of how full database backup is restored.

First, restore full database backup, differential database backup and all transactional log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option.

Following is the sample Restore Sequence

RESTORE DATABASE DATABASE FROM full_database_backup WITH NORECOVERY; RESTORE DATABASE DATABASE FROM differential_backup WITH NORECOVERY; RESTORE LOG DATABASE FROM log_backup WITH NORECOVERY; -- Repeat this till you restore last log backup RESTORE DATABASE DATABASE WITH RECOVERY;

I hope now it is very clear to you all what is restore sequence and the difference between recovery options.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 4 Comments

4 Responses

on July 15, 2009 at 3:51 pm | Reply aasim

tipo mistake

“which can create itegrity issues”
—————–
integrity
on July 16, 2009 at 4:35 pm | Reply Kuldip

Really nice article.
Thanks a lot.
on August 26, 2009 at 12:04 pm | Reply Rajiv Singh

Hi Pinal,

For recovery process as you wrote a code

RESTORE DATABASE DATABASE FROM full_database_backup WITH NORECOVERY;
RESTORE DATABASE DATABASE FROM differential_backup WITH NORECOVERY;
RESTORE LOG DATABASE FROM log_backup WITH NORECOVERY;

But as My experiance, The last lag_backup must be WITH RECOVERY mode

RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\FULL_CoreData.bak’ WITH REPLACE, NORECOVERY;
RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\DIFF_CDATA.BAK’ WITH REPLACE, NORECOVERY;
RESTORE DATABASE COREDATA FROM DISK=’H:\CDATA\CDATA_tail.bak’ WITH REPLACE, RECOVERY;

With Regards,
Rajiv Singh
on October 29, 2009 at 12:29 pm | Reply MARCOS

THIS IS WHAT PINAL TOLD US:

READ AGAIN:

– Repeat this till you restore last log backup
RESTORE DATABASE DATABASE WITH RECOVERY;

ALLWAYS THE LAST WILL BE WITH RECOVERY.

AND NEVER FORGET TO VERIFY ONLY YOUR BACKUP FILES BEFORE RESTORING.

REGARDS,
MARCOS ROSA