The log or differential backup cannot be restored because no files are ready to rollforward.
If you have found this page, it is likely that you encountered the following error when you tried to restore a differential backup using Microsoft SQL Server 2005.
Restore failed for Server ''. (Microsoft.SqlServer.Smo)
Additional Information:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)
What this error is telling you is that there is no database that was left in non-operational mode, and thus has not been cleaned up such that uncommitted transactions have not been rolled back.
The easy way to reproduce this error is to backup your database using full recover model, and do full and differential backups. Once you have your full and differential backup files you, if you want to restore your database all you have to do is restore the full backup first, and then one of the differential files (differential backups have all the changes since the last full backup) that brings you up to the point you want to restore to.
You will get the above error when you try to restore the differential backup (after you just restored the full backup).
Unfortunately, you forgot one critical detail (just like I did at first). You MUST restore all but the last (in this case the full backup) with NORECOVERY option.
In the Microsoft SQL Server Management Studio there are three options on the Option "page" when you restore a database.
Option 1 (the default): Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)
Option 2: Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY)
To properly restore a database that is backup up using full recovery mode with full and differential backups, here are the steps you need to follow to not get the above error.
Restore Full backup
- Open the Restore Database window in Microsoft SQL Server Management Studio
- Ensure the To database field is filled in with the name you want.
- Choose From device as the Source for restore.
- Choose the full backup file you want to restore. In most cases this is the most recent full backup file.
- Click the Options page on the left navigation.
- Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY). This is the most important step!!!
Restore Differential backup
- Open the Restore Database window in Microsoft SQL Server Management Studio
- Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup
- Choose From device as the Source for restore.
- Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
- Click the Options page on the left navigation.
- Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) Make sure to choose this if you want to use your database after the restore.
That is it, no more error. If you are lucky :)
74 comments:
Very useful! Thanks a bunch!
Anonymous,
Thank you very much for your feedback. I love the feedback. Thank you!
Brent
After the restoration of the full backup, The database get stuck in loading state and I did the differential backup restore even at DB loading state as instructed. Then I got the error that restoration can not be applied.
Hi Anonymous,
That has never happened to me. Maybe your back up is corrupt. Try generating the sql script to do the restore. That may give you more information. Keep me posted if you figure it out.
Good luck,
Brent
Thank u very much. Just to update u about the issue. I have run a script that specify WITH RECOVERY and I am able to get back the database. But the challenge now is that the differential backup cannot be applied. Can u please give me the script to apply full backup and then the differntial backup.
You can do the same thing in the user interface as the script as far as all this is concerned, but you may get different feedback. That is the only reason I suggested a scripting it out and trying that. Assuming you have a full and one differential back, here are the two commands that should work for you. You will need to adjust them to your specific situation.
RESTORE DATABASE [Test] FROM DISK = N'D:\Backup\Test.full' WITH FILE = 1, MOVE N'Test' TO N'D:\Data\Test.mdf', MOVE N'Test_log' TO N'D:\Log\Test_1.ldf', NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [Test] FROM DISK = N'D:\Backup\Test.diff' WITH FILE = 1, MOVE N'Test' TO N'D:\Data\Test.mdf', MOVE N'Test_log' TO N'D:\Log\Test_1.ldf', NOUNLOAD, STATS = 10
GO
The other thing you might look at is to make sure you are doing the full and differential back properly. Maybe try doing a full and differential backup on another test database and try your backup and restore procedure. That will rule out a configuration problem, and may very well point the issue to be corrupt on incomplete backup information.
I hope this helps.
Brent
Brent,
This post is very useful for me. Explantion is awesome.
You have clearly mentioned where is the mistake (using RECOVERY while restoring full backup).
Thanks you very much,
Murali krishna Reddy
Murali,
Thank you very much for the kind words. It is much appreciated. I am glad to help.
Brent
Excellent article, very easy to follow, and to the point. You saved my day! I'm really thankful, please keep writing like this!
Anonymous,
Thank you very much for the kudos. Comments like help fuel my fire to continue writing this stuff. Thank you very much.
Brent
This article saved my ass... if you are new to SQL restore it is a mess! THANKS
Anonymous,
Thank you for the feedback. So glad it was able to get you out of a pinch.
Thank you.
Brent
Only one word genius!!
DAO
Anonymous,
Thank you for the oh so kind words or word in this case. ;)
Brent
I love u!
I love u !!
I love U!!!
Kenneth,
Thank you very much. I am feelin' the love. ;)
Thx for the the feedback.
Brent
Remarkable article my problem is solved will u pls explain how it happened i mean little bit detail about how it works
Awesome! Thanks a lot!
Hi Philip,
Thank you for the kudos.
Brent
Hi Philip,
Thank u for our worth full idea...thanks lot..
By
S.K.Raj
Chennai.
Thank you
Hi Brent,
Thanks for this great tip.
I had got stuck after restoring the full backup and got this error for incremental backup.
Thanks once again.
Many Thanks
Worked like a charm :). Thanks!
This was very helpful. I was testing my hourly differentials in a test environment and I got this error. Better to see this error now instead of during a real crisis.
Worked like a charm.
Be proactive system Admins...
This was very helpful. I was testing my hourly differentials in a test environment and I got this error. Better to see this error now instead of during a real crisis.
Worked like a charm.
Be proactive system Admins
Thanks! Saved my bacon.
Brent,
Excellent Article. Thanks a Bunch!
Raj
perfect. Thank you.
Very useful. Thanks a lot!
This really helped me a lot and save my valuable time.
Keeping posting such useful blogs.
Thanks a lot!
Thanks a lot (n_n)
Thank you! Saved my life!!
For such issue there is mdf recovery. It can work out trouble with corrupted or lost sql databases. The software works under any Windows OS. It uses effective methods of restoring files from any SQL Server versions.
Hi Brent,
thanks a lot, it helped great deal here. Finally I do not understand why restoration of differential backups is not documented better in the restore wizard.
cheers,
Alex
Simple and it Works! :-)
Thanks
Thanks alot. this really helped.
Thankyou for a well written blog. Just what I needed (especially the morning after a bank holiday!)
Thank u so much
step by step instruction was fantastic
it's very useful. a massive thanks for the same.
Great article, helpful to me as well.
Great explanation in a perfect layout.
You're a life saver!
Thank you...
Thank you...
Thank you...
Thank you...
Thank you, man! You made my day!
THANK YOU!!!!!!!!!!!!!
Hello Brent V,
You have covered an informative topic in this article. I too have faced this error when I was trying to restore a differential backup WITH RECOVERY option. After reading this article I have resolved my problem.
THANK YOU SO MUCH!!!! This was driving me crazy and after finding your article I was able to restore my database differential backup when I had basically lost hope that it was going to work. I thought I was going to loose my mind. You rock!!!
Excellent. Precisely what I needed.
Kind regards.
Excellent post...fixed the issue.
Thanks & regars
Perfect! Resolved my issue! Thanks!
What a relief to find such a great link. As usual, I went to Microsoft site and was lost in so many considerations. Here the step by step is do easy to follow.
Many thanks Brent.
Didier
I just used this tip again. Very useful.
Thank you very much!!! All works! =)
Just ran into this error, and your page was the first result on google.
Really helpful post, thank you very much.
Thanks a lot... Issue Resolved :)
Thanks! Luckily no errors after this...
Perfect, clear instructions. Exactly what I needed. Thanks Brent!.
Shaun.
Good, Great, Wonderful, Amazing, Fantastic, and other best adjectives that i am remember now.
Thanks in advance man, you save my day.
Great answer to a question I desperately needed answered. I know this is an old post so I hope this gets answered.
If I have a full backup with 3 diff backups made after, do I need to restore all of the diffs ins sequence or just the most recent diff?
Hi brent,
I did exactly what the instruction said, but i got stuck with the database status saying (Restoring..). any idea? thanks.
I works Perfect!
I works Perfect! thanks
Awesome thanks!!!!
Thanks for this!
Thanks for this. Great...
Awesome article........thanks!
Muchas Gracias Greg, solo una consulta, para ver si lo eso haciendo bien, la primera BD completa seleciono la opción 'Sobre escribir la Base datos existente' y al restaurar la Base Datos diferencia, no selecciono dicha opción ¿Estoy bien? de antemano muchas gracias.
Cloud Ace Technologies is offering Implementation Services on Cloud Computing, Cloud Services, IT Security, Storage solutionsCommvault Backup Solution in chennai
Not ready to Start MS SQL Server? Contact to Microsoft SQL Server Support
One of MS SQL Server client grumbles that, he effectively downloads and introduces the MS SQL Server however shockingly he can't make sense of how to run it. He is additionally not ready to run his inquiries because of some reason. As a matter of fact this occurs with the vast majority of the clients. Be that as it may, simply pause; we have best choice to take care of this issue by Cognegic's Remote Infrastructure Management Support for Microsoft SQL Server and Online MS SQL Server Support. We manage these sorts of specialized hiccups superbly.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Not ready to take DB Backup in SQL Server Management Studio? Contact to DB Recovery Support
This appears to be extremely odd making basic inquiry to take the DB Backup in SQL Server Management Studio. Anyway taking reinforcement isn't so troublesome however it requires some sort of aptitude yet in the event that you don't have the foggiest idea about the procedure how to take reinforcement at that point unobtrusively contact to DB Recovery Services or Exchange Database Recovery to get back your information. We utilize straightforward methodology to determine your concern with propel procedures and with moderate costs.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Hi...First time differential backup is restoring successfully...but when i am try to next time it is not happening...it returns the error like
"Msg 3117, Level 16, State 4, Line 12
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally."
What this error is telling you is that there is no database that was left in non-operational mode, and thus has not been cleaned up such that uncommitted transactions have not been rolled back. The easy way to reproduce this error is to backup your database using full recover model, and do full and differential backups. Once you have your full and differential backup files you, if you want to restore your database all you have to do is restore the full backup first, and then one of the differential files (differential backups have all the changes since the last full backup) that brings you up to the point you want to restore to.
Post a Comment