Restore differential backup error - no files are ready to rollforward

Question

I'm trying to restore a differential backup of a test database, but I'm getting a error saying:

What I have done is created a test database, called "tdb", where I added some data in a new table, changed the recovery model of the database to SIMPLE and then created a FULL Backup.

After that, I added some more data into a new table and continued with making a differential backup.

When restoring the database I restored the full backup first, chosing the Overwrite existing database (WITH REPLACE) option from SSMS, which worked correctly.

Then when trying to do the same thing with the differential backup file, I get the above error.

I've looked at the Microsoft documentation as well and I've followed the same steps mentioned there, but I don't see what I've done wrong. Any help is appreciated.

Answer 1

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 ;)