sql differential backup cannot be restored because no files are ready to roll forward

I am trying to restore the differential backup and I get the following error
sql differential backup cannot be restored because no files are ready to roll forward

I am using SQL 2017. Please assist.
Star79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ayoub RouziCeo & CoFounderCommented:
You have to restore the full with NORECOVERY and restore the diff with RECOVERY.

A differential is no good without the full backup that goes with it. The differential backup contains the changed data 'since' the full. To restore the diff, you have to restore the full.

Restoring the full with NORECOVERY allows you to apply future diff or log restores. the very last restore of either a diff or log will need the with RECOVERY option to bring the database online.

Once you use the RECOVERY option on any restore scenario, you're done and no more restores can occur. The unfortunate thing is, RECOVERY is the default on a restore and if I had a dollar for every time someone in the world ran a long restore and forgot to use NORECOVERY only to find out that fact when they tried to apply diffs or logs, I would be on a beach somewhere and not on this site ;)
0
dfkeCommented:
Hi,

that's a blunt copy and paste solution from https://dba.stackexchange.com/questions/147297/restore-differential-backup-error-no-files-are-ready-to-rollforward?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

It's always nice to give the original person credits and link to their solution.

Anyways it's worth a try.

Cheers
0
Scott PletcherSenior DBACommented:
1) Use commands not the gui to do restores.  I think you can have the GUI create/show you the command rather than having to write it yourself from scratch.  Or post the relevant info here and we can write the command for you.

2) Specify NORECOVERY on ALL full, differential and/or log restore(s).
(Thus, this statement:
the very last restore of either a diff or log will need the with RECOVERY option to bring the database online
is false.)


Here's the general structure of the commands you'll need to do the RESTORE:

RESTORE DATABASE restored_database_name /* does not have to be the same the db name that was backed up*/
FROM DISK = 'f:\path\to\file\fullbackup.BAK' WITH NORECOVERY, MOVE 'logical_file_name1' TO 'X:\path\to\file\physical_file_name1', MOVE ...

RESTORE DATABASE restored_database_name FROM DISK = 'd:\path\to\file\diffbackup.DIF' WITH NORECOVERY

--RESTORE tran log backup(s) here, if needed.

--Once you're sure both the full and the diff (and, optionally, logs) have been restored, do a final recovery on the db to make it usable.
RESTORE DATABASE restored_database_name WITH RECOVERY;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Star79Author Commented:
When I restore the database using 'no recovery', the sql database gets stuck in 'restoring' state. Then I have run the following command to open the database

RESTORE DATABASE DBNameWITH RECOVERY

Now when I do this I can restore the differential backup. . Please assist.
0
Star79Author Commented:
Just to correct the last sentence, I cannot do differential backup ....
0
Anthony PerkinsCommented:
When I restore the database using 'no recovery', the sql database gets stuck in 'restoring' state
That is expected and normal.

Just to correct the last sentence, I cannot do differential backup ....
Can you tell us what is preventing you?  Is it:
A. You get an error message.  If so what is it?
B. You do not have a differential backup to use.
C. Something else.

You really should post the script you are using to restore the database as Scott has suggested.  We will be in a better position to help you then.
0
Star79Author Commented:
I am using SQL server GUI restore option.

a) Ok so I did the restore of full backup now with no recovery and the database is stuck in 'restoring' state.

b) Now next what should I do? How do I restore the differential backup? FYI I don't see any error and also I have differential backup. All I want is to know how to restore the differential backup from here (where the database is stuck in 'restoring' state).
0
Scott PletcherSenior DBACommented:
Run a command to do it:

RESTORE DATABASE restored_database_name FROM DISK = 'd:\path\to\file\diffbackup.DIF' WITH NORECOVERY

As to how to do it thru the gui, I still say "don't", because:
1) the gui is quirky and sometimes gets weird errors or stalls during processing
2) you never have a record of what you've restored
3) you to re-do it all from scratch the next time you do a restore.

The gui is great for a lot of things, but not for restoring dbs.

With commands, you simply call up the script(s), make any needed adjustment, and run it.  And you also have a record for later of exactly what you restored.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Star79Author Commented:
Thank you All.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.