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 NORECOVERY and RECOVERY when restoring database and what is the restore sequence.
The fact is that one can easily go to MSDN and learn the difference, so I had 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 a succinct explanation of the difference.
In other words-
While doing a RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep the database in a state where more backup file is restored. This will also keep database offline also to prevent any changes, which can create integrity issues. Once all backup file is restored run RESTORE command with a 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 transaction 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 (https://blog.sqlauthority.com)
45 Comments. Leave new
tipo mistake
“which can create itegrity issues”
—————–
integrity
if I am not wrong , its “typo” not “tipo”
Really nice article.
Thanks a lot.
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:CDATAFULL_CoreData.bak’ WITH REPLACE, NORECOVERY;
RESTORE DATABASE COREDATA FROM DISK=’H:CDATADIFF_CDATA.BAK’ WITH REPLACE, NORECOVERY;
RESTORE DATABASE COREDATA FROM DISK=’H:CDATACDATA_tail.bak’ WITH REPLACE, RECOVERY;
With Regards,
Rajiv Singh
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
What if the last successful restore was completed in NORECOVERY, then you discover the final differential backup is corrupted… Can simply set the database to operational mode (truncating any open logs) or do you have to do something spectactular?
Thx!!!!
Hi Pinal,
Can’t we use the Standby Option when we restore the database so that we can apply the differential and log backups to bring the database into consistent state.
Regards
Vivek Singh
@Vivek.
When you restore a backup, you can choose from 3 restore option,
1. With Recovery – Database is ready to use, and user can connect to database, user can change data inside database.
2. No Recovery – Database is not ready, there are few more backups that has to be applied to this database instance. User cannot connect to database because it is in Restoring Status. (Exception: Not considering Database Snapshots )
3. Standby / Read Only – Database is ready to use but database is in Read Only mode, user can connect to database but they cannot change data inside database.
Now, if you have more backups to apply, definitely you want to choose with No Recovery Mode option when applying all your backups, and when you apply your last / final backup, then either you can restore with Recovery or Standby option.
~ IM.
How to change online db to standby mode
Hello Ahmad,
A running database con not be changed to standby mode. Only a data in no-recovery state can be moved to standby mode. This is an option that is specified while restoring a database or transaction log.
Regards,
Pinal Dave
Hi Pinal,
could you please help restore with standby mode t-sql
Thanks for the reply
I have to restore transcation logs to a secondary database every 30 minutes.Can i restore a transaction log backup when the database is in RECOVRY mode. ? i dont want to set the database in NORECOVERY Mode each time i carry out the restoration process.
Hello Rashmi,
Once a database converted in RECOVERY mode, no more transaction log backup can be restored. You can not set a database back to NORECOVERY mode.
So to resotre more transactional log backups keep the database in NORECOERY mode.
Regards,
Pinal Dave
Hi Pinal,
When a transaction log recovery is running on the database with recovery mode, can th database be accessible to other users, for example can we fire a select query to the database? If yes then what is the process to do so? Since, we tried that when we were running the recovery on the DB and we parallely fired a select query, the recovery got aborted.
Please suggest.
Our aim is that when we are restoring the transaction log the database should be accessible to the users.
Thanks in advance!!
Hello Rashmi,
A database in recovey mode can be made accessible using STANDBY option. This option is usually used in log shipping to make the secondary database available.
Regards,
Pinal Dave
It means a read only operational database is nothing but a database in stand by mode.
Hi Pinal Dave,
We have a Production DB Server (SQL Server 2000) and Reporting DB Server (SQL Server 2005)
Earlier both servers were having SQL 2000. But now they have upgraded Reporting DB Server to SQL 2005.
Backup Process – We have a process which syncs 5 databases between Production and Reporting. Complete restore is taken on a weekly basis (Sunday) and transaction logs are taken on a daily basis.
After upgradation of Reporting server to SQL Server 2005, backup process is not running as expected.
We were able to restore the complete backup successfully which happens weekly. But having some issues while restoring transaction logs.
While trying to restore the database and transaction logs , we have 3 status modes:
Recovery : We can’t use it, as it does not allow to restore transaction logs. If transaction logs are not there, we can use it very well for complete backup.
NoRecovery : It can be used to restore but again database is no useable. It will be in recovery mode.
StandBy : In the old reporting server(SQL 2000), they were using StandBy mode.
It is read-only mode. But we are not able to use it as it says a message “This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.”
As backup is from SQL 2000, it can’t be restored on SQL 2005.
We have restored the complete backup which was done using “recovery” mode. If complete backup is done in “recovery” mode, it is not restoring transaction logs.
Could you please help me out in resolving this issue
Hello Ravi,
You can not use WITH STANDBY clause to restore a SQL Server 2000 database in SQL Server 2005.
You would have to upgrade the source server to 2005.
Regards,
Pinal Dave
Hi Pinal,
yes i understand i cant use standby clause. Currently my db full backup restored using recovery mode so that the users can access the db after restoration. This happens on a weekend. Now we are unable to restore the daily transactional logs as the DB is in the recovery mode. In this scenario we have following questions
1. how can we change the mode of the DB to No recovery?
2. Is there anyother way that we can restore the transaction logs when the DB is in recovery mode?
3. From some of the article i found out that, the transaction logs should be restored with “no Recovery” and the final transaction log should be restored with “Recovery”
Hi,
I have current Live server which from now I want to use as Secondary for Log shipping. and then setup a new server as Primary.
But I don’t know how to convert current live to take in NoRecovery Mode without data loss.
I am very confused in this.
Thanks!
Thanks Very Much you always support us Mr pinal