Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mohan.bndr
mohan.bndr
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 473
Hi All,

Have one situation...

If we take full backup .. will it be affect the LSN Chain. Please reply me
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3053 Visits: 3633
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’! **
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3053 Visits: 3633
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’! **
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
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


kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 923
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search