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
Which mode i can use when differential back up ?
recovery or no recovery
A best practice is to restore all log files with NORECOVERY.
Then, follow that with:
restore database with recovery
Microsoft suggests this practice to prevent accidental recovery before all logs are restored.
A portion of my reply was removed when I posted.
The command syntax is
RESTORE DATABASE dbname WITH RECOVERY
where dbname is the name of the database you are recovering.
I have restored Database with No recovery option , its showing in Restoring mode seems like hanged in Restoring Mode i wanna bring it to Standby mode
how to achieve it
Dear All,
My sql server database goes to recovery mode on every 2-3 hours. current database size is 3173 MB.
Could you please any one help to resolve this?
Thanks
Hi Everyone,
I tried to restore SQL Server 2000 xxx.bak database on another database. The code is
Use Master
go
Alter Database xxx
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE xxx
FROM DISK = N’E:\Backup\xxxxx\xxxxxx.bak’
WITH FILE = 1,
MOVE N’xxx99_Data’
TO N’E:\MSSQLDATA\exercises_edi.mdf’,
MOVE N’xxx99_Log’
TO N’E:\MSSQLLOGS\exercises_edi.ldf’,
NOUNLOAD,
REPLACE,
STATS = 10
GO
The process got disconnected in the middle and still the database appears to be in “restoring mode”. How can I stop that mode and start again from scratch
Dear Pinal Dave,
can i restore LOG without restore the FULL backup at first, namely i need to restore a full backup first then i need to add new files to make partitioning on 2 tables, after that i need to restore LOG backup. so i need the database to be online but i will not make any transaction on database while it’s online.
it this double?
Hi Pinal Sir,
I have been a frequent visitor of your Site, and must say it is quite informative and helpful !! Hearty Thank You.
I was wondering if you could also let us know something about the “Security”
Regards,
Rukku
Hi Pinal Sir,
Please let me know, if there is a way to create a full backup daily with log backups/ differential backups.
My Primary Server is too far. I am sitting at the DR Site. I cannot transfer fullbackup daily. I need to take full backup and copy to tape.
Regards,
nvn
nice article… helped a lot..
sir,
in my office we hv been using ms sql server management studio as a backend.
each time after inputting id and paswd an error occurs ” sql server does not allow remote connection” i.e error 26 ..please suggest any solution..i hv checked the tcp/ip also..bt still the problem is same.
Hi Pinal,
Once the database is changed to recovery(online) from norecovery, why can’t we apply additional backups to the database. What has been changed or why sql server does not allow to restore additional backups after a recovery? Does LSN number change after a recovery happens from a backup. Can you explain the reson.
Thanks in advance
Dear Pinal
I want to restore a specific table from my existing backup. Is it possible in SQL Server
No @Nisarg. Object level recovery is not available in SQL Server yet
Hi,
Question. I have Full Backup, Differential Backups and Transactional Logs.
When I do RESTORE,
1) Do RESTORE for Full Backup
2) Do I have to restore all differential backups? or just specific ones?
3) Do I RESTORE all Transactional Logs after the last diff only?
Thanks!
1. Full backup is needed to restore any other backups after that.
2. only last differential backup would have all changed since last full backup.
3. Correct. Full > last diff > all t-log backups after diff
Thank you very much! It help me a lot with a recovery issue that I had…
Perfect. Nice to hear that.
Hi,
I would like to ask a question here.
We have both LIVE and UAT environment and Databases for our Systems. We take Full nightly backup (every day), Differential backups every 2 hours and Transaction backups every 30 minutes.
Is this possible to take Full, Differential and Transactions backups of our LIVE Systems and restore them into our UAT environment? We tried this and managed to restore Full backup but when we tried to restore Differential and Transaction backups, we got error message, The error was related to LSN sequence.
How we can overcome this issue?
Thanks
It would work. I am guessing that its a large database. LSN error would come if you miss a backup in the middle. Look at the backups taken as I am guessing there are two backup jobs or two destination of backups.
Thanks for your reply.
I tried this again. I copied Full, Differential and Log backups from LIVE environment and tried to restore them into UAT environment. I managed to restore Full backup, but when I tried to restore Differential and Transaction Log backups, I’m getting following error message:
“Unable to create restore plan due to break in the LSN chain.”
I’m sure I haven’t missed any backup in between.
Any idea, why this is failing?
Thanks
Check https://sqlserver-help.com/2014/05/29/help-getting-error-unable-to-create-a-restore-plan-due-to-break-in-the-lsn-chain/
Hi Pinal
I need to restore trans logs every 2 hours in my reporting server. Once I do the full backup restore and set it to recovery, I am unable to restore the transactional logs. I get error – The log or differential backup cannot be restored because no files are ready to rollforward.
Can you please suggest what need to be done in this case to allow the trans log to be applied and bring it online every 1 hour or 2 hrs. The database is used purely read only. I am restoring from LIVE backup and log backup taken every 2 hours to the report server.