Monday, March 30, 2009

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
  1. Open the Restore Database window in Microsoft SQL Server Management Studio
  2. Ensure the To database field is filled in with the name you want.
  3. Choose From device as the Source for restore.
  4. Choose the full backup file you want to restore. In most cases this is the most recent full backup file.
  5. Click the Options page on the left navigation.
  6. 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
  1. Open the Restore Database window in Microsoft SQL Server Management Studio
  2. 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
  3. Choose From device as the Source for restore.
  4. Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
  5. Click the Options page on the left navigation.
  6. 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:

Anonymous said...

Very useful! Thanks a bunch!

Brent V said...

Anonymous,

Thank you very much for your feedback. I love the feedback. Thank you!

Brent

Anonymous said...

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.

Brent V said...

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

Anonymous said...

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.

Brent V said...

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

Anonymous said...

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

Brent V said...

Murali,

Thank you very much for the kind words. It is much appreciated. I am glad to help.

Brent

Anonymous said...

Excellent article, very easy to follow, and to the point. You saved my day! I'm really thankful, please keep writing like this!

Brent V said...

Anonymous,

Thank you very much for the kudos. Comments like help fuel my fire to continue writing this stuff. Thank you very much.

Brent

Anonymous said...

This article saved my ass... if you are new to SQL restore it is a mess! THANKS

Brent V said...

Anonymous,

Thank you for the feedback. So glad it was able to get you out of a pinch.

Thank you.

Brent

Anonymous said...

Only one word genius!!

DAO

Brent V said...

Anonymous,

Thank you for the oh so kind words or word in this case. ;)

Brent

Kenneth said...

I love u!
I love u !!
I love U!!!

Brent V said...

Kenneth,

Thank you very much. I am feelin' the love. ;)

Thx for the the feedback.

Brent

Anonymous said...

Remarkable article my problem is solved will u pls explain how it happened i mean little bit detail about how it works

philip said...

Awesome! Thanks a lot!

Brent V said...

Hi Philip,

Thank you for the kudos.

Brent

s.k.raj said...

Hi Philip,
Thank u for our worth full idea...thanks lot..

By
S.K.Raj
Chennai.

Dejan said...

Thank you

Nimesh Thakkar said...

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.

Anonymous said...

Many Thanks

Anonymous said...

Worked like a charm :). Thanks!

Anonymous said...

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...

Anonymous said...

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

Anonymous said...

Thanks! Saved my bacon.

Anonymous said...

Brent,

Excellent Article. Thanks a Bunch!

Raj

CS said...

perfect. Thank you.

Anonymous said...

Very useful. Thanks a lot!

Alex said...

Thank you!

Babar Javaid said...

This really helped me a lot and save my valuable time.

Keeping posting such useful blogs.

Anonymous said...

Thanks a lot!

Anonymous said...

Thanks a lot (n_n)

Riaad said...

Thank you! Saved my life!!

Alex said...

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.

RedM4st3r said...

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

RedM4st3r said...
This comment has been removed by the author.
Nuno Belo said...

Simple and it Works! :-)
Thanks

Anonymous said...

Thanks alot. this really helped.

Anonymous said...

Thankyou for a well written blog. Just what I needed (especially the morning after a bank holiday!)

Anonymous said...

Thank u so much
step by step instruction was fantastic

uppu said...

it's very useful. a massive thanks for the same.

Will Bellman said...

Great article, helpful to me as well.

Anonymous said...

Great explanation in a perfect layout.

You're a life saver!

JP Negri said...

Thanks a lot!

Anonymous said...

Thank you...
Thank you...
Thank you...
Thank you...

jairofx said...

Thank you, man! You made my day!

Anonymous said...

THANK YOU!!!!!!!!!!!!!

Adam Gorge said...

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.

mknopf said...

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!!!

Anonymous said...

Excellent. Precisely what I needed.

Kind regards.

Anonymous said...

Excellent post...fixed the issue.

Thanks & regars

Anonymous said...

Perfect! Resolved my issue! Thanks!

Le Grand DIC said...

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

Anonymous said...

Thank you

Brent V said...

I just used this tip again. Very useful.

Вячеслав Паршин said...

Thank you very much!!! All works! =)

Obscuria Cyannis said...

Just ran into this error, and your page was the first result on google.

Really helpful post, thank you very much.

ABhishek said...

Thanks a lot... Issue Resolved :)

kaur said...

Thanks! Luckily no errors after this...

Shaun H. said...

Perfect, clear instructions. Exactly what I needed. Thanks Brent!.

Shaun.

Anonymous said...

Good, Great, Wonderful, Amazing, Fantastic, and other best adjectives that i am remember now.

Thanks in advance man, you save my day.

Marshall Youngblood said...

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?

Anonymous said...

Hi brent,

I did exactly what the instruction said, but i got stuck with the database status saying (Restoring..). any idea? thanks.

Daniel Gasca said...

I works Perfect!

Anonymous said...

I works Perfect! thanks

Preeti said...

Awesome thanks!!!!

Anonymous said...

Thanks for this!

Anonymous said...

Thanks for this. Great...

Greg said...

Awesome article........thanks!

Anonymous said...

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.

Swarup said...

Cloud Ace Technologies is offering Implementation Services on Cloud Computing, Cloud Services, IT Security, Storage solutions Commvault Backup Solution in chennai

john mike said...

Cloud Ace Technologies is offering Implementation Services on Cloud Computing, Cloud Services, IT Security, Storage solutionsCommvault Backup Solution in chennai