0

I run a bad query on my production server and I need to perform a restore of my database.

I had a full backup (20160828) and a diff backup (29,30,31 and 01/09) and I had transaction log backup.

Is it necessary to restore all the diff backups or only the last one?

Is it true that all restore operation I have to do must be in non recovery (the full, all the diff and all the transactionlog) except the last transaction log backup which must be restore with recovery?

Thanks in advance

  • 2
    There is an expression. "Your backup strategy is only as good as the last time it was tested." Sounds like 1. no plan and that 2. it was never tested. I wish you the best of luck (sincerely). – Igor Sep 1 '16 at 18:07
7

Just restore the last full backup, the most recent Differential and then the transaction logs after the Diff, up to the point in time you want to restore. And Igor is right.

| improve this answer | |
5

Complementing @WEI_DBA's answer, I find it highly useful if you have a script that generates exact command to run.

sp_RestoreGene is incredibly useful in situation that you are in (provided you have good restore-able backup files).

This procedure queries msdb database backup history and database file details in master. It builds and returns RESTORE DATABASE commands as its result set, it does not execute the commands.

Make sure you use @StopAt to stop the restore before you said "Opps This was a mistake...!"

Is it necessary to restore all the diff backups or only the last one?

Latest FULL, latest diff and subsequent T-log backups.

Is it true that all restore operation I have to do must be in non recovery (the full, all the diff and all the transactionlog) except the last transaction log backup which must be restore with recovery?

Yes, all restores should be done using norecovery. The last T-log should be done using recovery to bring the database online.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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