2

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

enter image description here

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.

0

1 Answer 1

8

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.