3

I need to check some old data. I need to check data from last night compared to data today.

I am going to use SQL Data Compare to do this but I need to setup a fresh database to restore the backup to so I can compare it against the existing database.

My question is if I have a full backup from 4-5 days ago, and a .dif for each day after that up until today, do I need ALL the .difs up to the point I want to restore or will the .dif from that day be sufficient? I assume I will need the most recent .bak as a basis for the new DB?

MetaGuru
  • 896
  • 6
  • 22
  • 36

1 Answers1

5

A differential backup contains all the changes made to the database since the last full backup.

You only need to restore the full backup and the last differential one.

Massimo
  • 70,200
  • 57
  • 200
  • 323
  • How do I restore the .dif once I get the new DB up and running from the .bak? I keep getting this error: "The log or differential backup cannot be restored because no files are ready to rollforward. – MetaGuru Aug 26 '10 at 19:58
  • @Ryan: have a look at this MSDN article: http://msdn.microsoft.com/en-us/library/ms175510.aspx – squillman Aug 26 '10 at 20:09
  • 1
    @Ryan: when you restore the full backup, you should select the option that say something similar to "I have to restore additional backups, don't bring the database fully online". The exact name of the option changes with the SQL Server version, in 2008 R2 it's "Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.". – Massimo Aug 26 '10 at 20:16
  • thanks, this worked: http://justgeeks.blogspot.com/2009/03/log-or-differential-backup-cannot-be.html – MetaGuru Aug 26 '10 at 21:06