Post reply

restore db from multiple diff vs last diff

  • December 13, 2012 at 10:37 am

    #286207

    which option (below) will the restoring the 2nd (last) diff be faster?

    option 1: restore full (10TB), 1st diff (3TB), 2nd (last) diff 5TB

    option 2: restore full (10TB), 2nd (last) diff (5TB)

    With option 1: I already tried this and it know it worked. I'm wondering behind the scene: when restoring the last diff, does sql engine know where it left off, so it only applying 2TB (5TB - 3TB) changes, instead of applying the whole 5TB and overwrite the 1st diff?

    This is useful to know when I have a huge db and want to migrate to new server... and I want to start the restore (w/ norecovery) ahead of time

    Thanks,

    Thanh

  • December 13, 2012 at 11:03 am

    #1568416

    You only ever need to restore the last differential backup. They're differentials, not incrementals. Restore the full backup, the last differential then, if you're restoring log backups as well, all log backups since that last diff

    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

  • December 13, 2012 at 11:22 am

    #1568428

    Unless you need to restore to an earlier state (point-in-time restore), the last diff is the only one you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 13, 2012 at 12:09 pm

    #1568438

    Thanks for the replies.

    My question was in which option would my 2nd-diff restore finish faster? Or they would take the same amount of time to restore the 2nd-diff?

    For example: My full back taken Sunday 10TB, Wed 1st diff 3TB, Friday 2nd diff 5TB. Saturday afternoon is the migration day. Just make things simpler, just leave TL out of the picture for now. My key point here is the time it would take to restore the 2nd diff. So which option would make my 2nd-diff restore complete faster in term of duration?

    Thanks,

  • December 13, 2012 at 12:15 pm

    #1568439

    Let me put it this way:

    A differential backup contains all the changes since the last full backup. It has no reference, checks, no knowledge of what changed since a previous differential backup. When you restore a differential backup, that differential is restored in its entirety because there is no other possible option.

    Hence, first option:

    Restore full backup, restore 3TB of differential, then restore 5 TB of differential

    Second option:

    Restore full backup, restore 5 TB of differential

    If you want incremental restores, you want log backups. They are incremental, each one contains changes since the last log backup.

    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

  • December 14, 2012 at 3:58 pm

    #1568851

    Thanks Gail.

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

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