How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups???

  • '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

  • 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).

  • Lynn Pettis (4/4/2014)


    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).

    thanks for the quick reply Lynn,

    had thought to try the umpteen log file restore, however the sql error when trying a trans log restore of the first logfile created after last diff day1 (so; full, diff, log1) is:

    "...

    System.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 2804703000000039200001, which is too early to apply to the database. A more recent log backup that includes LSN 2804992000000193600001 can be restored

    ..."

    yet none of the more recent logfiles work either.

  • Are you sure the log backups and the full/differential are from the same database on the same server? There has to be a log that overlaps with the LSN on the differential if you have all the logs and they're from the same source.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • mark 95074 (4/5/2014)


    "...

    System.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 2804703000000039200001, which is too early to apply to the database. A more recent log backup that includes LSN 2804992000000193600001 can be restored

    ..."

    yet none of the more recent logfiles work either.

    Sounds like either you are missing a log file or the log chain was broken somehow. Check the MSDB backup histories for the backups which exist, you should be able to see if you're missing any files, also check the error log make sure there are no entries about the recovery model being changed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks to all.

    turned out to be an impossibility in the end; logs created post restore belonged to 'new' db, no longer applicable to original backup. missing data recovered using db diff tool.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply