Restore Sequence

  • If i have a FULL backup on sunday evening @ 8:00 PM and succeeding a Differential backup @ 7:00 AM everyday(MON,TUE,WED,THU,FRI) with this LOG Backup for every 4 hours each day and my database(EX: TESTDB) had crashed at 6:00 PM on Friday what is the Restore sequence

    Ans : First a Full backup with no recovery,

    step 2 : All the Differential backups since last full backup with no recovery,

    step 3: All the log backups since last full backup with recovery, STOPAT <time or LSN number>

    is this correct or not?

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Save a tree... Please don't waste paper unless you really need to!

    When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'

  • You don't need all the differentials since the last full, just the latest one.

    This is what you need:

    1. Last full backup

    2. Last differential

    3. All the log backups taken after the differential

    -- Gianluca Sartori

  • spaghettidba (6/8/2015)


    You don't need all the differentials since the last full, just the latest one.

    This is what you need:

    1. Last full backup

    2. Last differential

    3. All the log backups taken after the differential

    Restoring just the last differential doesn't sound correct to me, but I've been wrong before.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (6/8/2015)


    spaghettidba (6/8/2015)


    You don't need all the differentials since the last full, just the latest one.

    This is what you need:

    1. Last full backup

    2. Last differential

    3. All the log backups taken after the differential

    Restoring just the last differential doesn't sound correct to me, but I've been wrong before.

    Diff backup takes all the pages marked for the differential base, which is the pages changed since the last full backup. A new full backup resets the differential base. See here for details: https://msdn.microsoft.com/en-us/ms175526.aspx

    -- Gianluca Sartori

  • Alvin Ramard (6/8/2015)


    spaghettidba (6/8/2015)


    You don't need all the differentials since the last full, just the latest one.

    This is what you need:

    1. Last full backup

    2. Last differential

    3. All the log backups taken after the differential

    Restoring just the last differential doesn't sound correct to me.

    It is correct. Differentials contain all changes since the full that they're based on, hence it's a pointless waste of time to restore multiple diffs one after the other (though you can do it)

    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

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

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