Full backups on any database, will it affect the LSN chain ?

  • Hi All,

    Have one situation...

    If we take full backup .. will it be affect the LSN Chain. Please reply me

  • Any full backup will break the LSN chain, unless it's executed with the COPY_ONLY option. But this breaking of the LSN chain only applies to the DIFF backups. If you have all the LOG backups available since 2 or 3 FULL backups before, you can still use this to restore to the current point-in-time.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi (7/17/2013)


    Any full backup will break the LSN chain, unless it's executed with the COPY_ONLY option. But this breaking of the LSN chain only applies to the DIFF backups. If you have all the LOG backups available since 2 or 3 FULL backups before, you can still use this to restore to the current point-in-time.

    I beg to differ with you. Full and Differential backups WILL NOT break your log chains.

  • Lynn Pettis (7/17/2013)


    I beg to differ with you. Full and Differential backups WILL NOT break your log chains.

    I agree that it won't break your LOG chain, maybe I used the wrong words to explain my statement well. I assumed the OP was referring to how to restore when a unexpected backup was taken (and backup file not available). Therefor I also tried to describe an example.

    A log chain can only be broken by changing the recovery model to simple and back to full or bulk-logged. For more information about LSN to plan a restore, look at: http://msdn.microsoft.com/en-us/library/ms190729(v=SQL.105).aspx

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Full backups do not and never have affected the LSN chain for log backups in any way, means, guise or form (with the sole exception of the first full backup ever taken)

    You can take as many full backups as you like, copy only or no copy only and they will not affect the LSN chain at all.

    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
  • HanShi (7/17/2013)


    I agree that it won't break your LOG chain, maybe I used the wrong words to explain my statement well. I assumed the OP was referring to how to restore when a unexpected backup was taken (and backup file not available). Therefor I also tried to describe an example.

    Except that the example has nothing to do with the LSN chains that the OP asked (full backups reset the differential base, that's all)

    A log chain can only be broken by changing the recovery model to simple and back to full or bulk-logged.

    It can be broken by a number of other things than just that, but full backups are not one.

    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
  • mohan.bndr (7/17/2013)


    Hi All,

    Have one situation...

    If we take full backup .. will it be affect the LSN Chain. Please reply me

    No. Only a transaction log backup will break the chain.

  • kevaburg (7/19/2013)


    mohan.bndr (7/17/2013)


    Hi All,

    Have one situation...

    If we take full backup .. will it be affect the LSN Chain. Please reply me

    No. Only a transaction log backup will break the chain.

    Actually, no. A transaction log backup does not break the chain. The chain can be broken, however, if a transaction log backup becomes corrupt or lost.

Viewing 8 posts - 1 through 7 (of 7 total)

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