Home Forums SQL Server 2008 SQL Server Newbies How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups??? RE: How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups???

  • April 4, 2014 at 7:42 pm

    #1703459

    mark 95074 (4/4/2014)

    'morning all

    I've had a report of missing data (restore from full was done, diff wasn't applied) in a db that has the following backup schedule:

    daily full @10pm

    6 hourly diffs (12/6/12/6)

    15min log

    I now have to restore the db as at 4 days ago, then restore all data added/changed since then.

    In testing, have found i can no longer restore the last diff from day 2, ie:

    restored full from day 1 (norecovery), restored last diff from before next full backup (norecovery), and now trying to restore last diff from day2 (taken after 2nd full), and getting the well documented error:

    "This differential backup cannot be restored because the database has not been restored to the correct earlier state"

    I understand the reason for the error, but what is the procedure for recovering in this situation? must I restore each subsequent full db as well? (overwrite/no overwrite, norecovery?)

    thanks in advance

    Differential backups are tied to the full back taken prior to them. After restoring with norecovery the the full backup from day 1 and the last differential taken before the next full backup (norecovery) you then have to start restoring all the transaction log backups taken after that differential backup (with norecovery on all except the last transaction log backup).