Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full backups on any database, will it affect the LSN chain ? Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 5:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:10 AM
Points: 104, Visits: 400
Hi All,

Have one situation...

If we take full backup .. will it be affect the LSN Chain. Please reply me
Post #1474497
Posted Wednesday, July 17, 2013 5:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1474511
Posted Wednesday, July 17, 2013 6:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 23,322, Visits: 32,066
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

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)
Post #1474540
Posted Wednesday, July 17, 2013 6:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1474554
Posted Wednesday, July 17, 2013 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 2008, MVP
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

Post #1474570
Posted Wednesday, July 17, 2013 6:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 2008, MVP
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

Post #1474574
Posted Friday, July 19, 2013 9:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:26 AM
Points: 311, Visits: 487
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.
Post #1475570
Posted Friday, July 19, 2013 11:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 23,322, Visits: 32,066
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.



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)
Post #1475620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse