Post reply

Restoring from broken backup chain

  • December 19, 2013 at 10:18 am

    #280469

    So I have a situation where a nightly job is making a full backup and disrupting our usual full-differential backup chain. The vendor will be fixing that shortly. In the meantime, I need to restore some backups of this database down to a Dev environment.

    Here's what I've got:

    Day 1: Evening Full backup

    Day 2: Morning Transaction Log

    Evening Differential backup (won't work with full because of broken chain)

    Day 3: Morning Transaction Log

    I need to get this database restored to morning of Day 3. If I use the Full and the two transaction logs, do you think I'll get all the data I need? Or am I doomed until the fix is in?

    (FYI: The backups that are breaking the chain are not available. They keep getting deleted after they are used.)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 19, 2013 at 10:36 am

    #1674928

    Restore full with norecovery

    restore logs, in sequence, with norecovery, stopat = 'time you want to restore to'

    Full backups do not break the log chain. Differential backups do not break the log chain

    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 19, 2013 at 10:58 am

    #1674932

    GilaMonster (12/19/2013)

    Full backups do not break the log chain. Differential backups do not break the log chain

    I know that. The chain that was broken was between the differential and the full. But I wasn't entirely sure if I could get what I needed from just restoring the logs to the full.

    Thanks, Gail. I appreciate the prompt answer.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 19, 2013 at 11:14 am

    #1674933

    Brandie Tarvin (12/19/2013)

    GilaMonster (12/19/2013)

    Full backups do not break the log chain. Differential backups do not break the log chain

    I know that. The chain that was broken was between the differential and the full. But I wasn't entirely sure if I could get what I needed from just restoring the logs to the full.

    The differential base was reset. The log chain was not broken. All you need to restore to any time is a full backup from before that time and an unbroken log chain.

    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 19, 2013 at 11:26 am

    #1674936

    Differential base... And so I learn a new term (that I arguably should have know before this).

    Thank you again, Gail. You are a star.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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