Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

Over the bank holiday the backups failed on a db the full has been restored and now I have several differentials to be restored on top.

Could someone give best method or do I restore just sequentially one after the other in T-SQL? Are there pitfalls that I should be aware of prior to doing this.

share|improve this question

marked as duplicate by Mark Storey-Smith, Marian, Paul White, Derek Downey, Max Vernon Apr 3 '13 at 15:09

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You should only need to restore a single differential -- I suggest reading up on how differential backups work before proceeding. – Jon Seigel Apr 3 '13 at 14:12

First thing you do is restore the FULL backup, and then you restore the latest DIFF backup. That's it! This is assuming no FULL backups were taken in between the DIFF and the FULL you are restoring from (only exception is if you used a COPY_ONLY option in your full backup but most likely that wasn't the case). Remember to use WITH NORECOVERY on the full restore so you could continue to restore the DIFF.

In the rare exception that the DIFF throws an error stating that the LSN's don't match and your full backup is not the right one, you can query MSDB or right click on the DB, go to 'reports', and 'backup/restore' history. That'll tell if a full backup was taken in between but by the sounds of it that is not the case. On the DIFF restore make sure to state WITH RECOVERY to replay the log.

share|improve this answer
Thanks Guys I hit the books and followed thru as I couldn't get to your site and guess what they say the same as you and it worked beautifully and the nightly restores worked in time for year end, but I will check more thoroughly though I did spend a good while before asking ( this is only my 2nd question asked) – user22066 Apr 6 '13 at 5:55
no problem, glad it helped. Feel free to ask. If you feel the answer was good to your questions you can mark them as answered so it shows in your profile and others can see. Good luck with SQL Server. – Ali Razeghi Apr 6 '13 at 7:56

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