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

How can you issue a full backup and not interrupt the LSN's Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 10:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:10 PM
Points: 99, Visits: 495
My two cents worth...I was reading an article the other day on junior dba questions and saw the question:

How can you issue a full backup and not interrupt the LSN's?
*Issue a copy only backup.
(http://www.mssqltips.com/sqlservertip/1626/junior-sql-server-dba-interview-questions/)

I have seen this answer many times and always thought it to be a half truth as I was a initially a bit confused about the role of copy_only backups when they first appeared and some answers mislead me a bit.

To me it reads as though any full backup done outside the daily full backup will break the chain, or will be needed in order to restore to point in time (or the last log backup).

Very happy to be corrected but is it true that the Log Chain is only interrupted by the full backup IF the backup regime does Differential backups?

If the backup regime only consists of a Full Backup and Log backups then you can do as many full backups as you want (and delete them) and the log chain will not be broken. i.e you can restore a backup from 6 months ago and restore to point in time as long as you can restore every backup log since in sequence -- regardless of continuing daily backups.

thanks
Post #1428393
Posted Thursday, March 7, 2013 11:05 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
So long as you have the tran log backups, you should be able to restore. But to play it safe, I'd keep those backups and perform copy_only backups outside of the regimental backup.

Just taking the cautious route.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428398
Posted Thursday, March 7, 2013 11:45 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
Full Backup taken Monday night 6:00 PM.
T-Log backups taken every 2 hours starting at 8:00 PM until 6:00 PM Tuesday.
Full Backup taken at 6:00 PM Tuesday.
Full backups taken every 2 hours starting at 8:00 PM.

System experiences a hdd failure at 9:00 AM Wednesday morning, you lose your production system database mdf file. Log file is good as it is on a separate disk drive. Perform a tail log backup of the log file.

After successfully completing the tail log backup you start a restore of the full backup taken Tuesday night at 6:00 PM. Restore fails, the backup file is corrupt, unreadable. Copy from tape is also corrupt.

Question, can you still restore your database to the point of the hdd failure.

Answer, yes. You start with the full backup taken Monday night and start restoring all the t-log backups taken since that full backup.

Full backups do not break the LSN chain for transaction logs.



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 #1428417
Posted Friday, March 8, 2013 12:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:10 PM
Points: 99, Visits: 495
When I check my backups I normally choose one from 2 or 3 days ago and apply every log since then.

Quite a few logs to restore but it doubles as a check for many log backup files and also keeps me refreshed on where the restore scripts are and how to use them (rather then learn in an emergency).

So although I knew that a copy_only backup did not need to be done (unless a diff backup is done) I was second guessing myself when the answer to the question in the article said it does need to be done to prevent the log chain breaking..thanks
Post #1428427
Posted Friday, March 8, 2013 3:12 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 @ 3:43 PM
Points: 43,047, Visits: 36,206
UncleBoris (3/7/2013)
Very happy to be corrected but is it true that the Log Chain is only interrupted by the full backup IF the backup regime does Differential backups?


The log chain is never interrupted by a full backup, regardless of the presence or absence of differentials.
http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/



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 #1428478
Posted Friday, March 8, 2013 8:52 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
GilaMonster (3/8/2013)
UncleBoris (3/7/2013)
Very happy to be corrected but is it true that the Log Chain is only interrupted by the full backup IF the backup regime does Differential backups?


The log chain is never interrupted by a full backup, regardless of the presence or absence of differentials.
http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/


Great write-up on it Gail.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428621
Posted Sunday, March 10, 2013 5:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:10 PM
Points: 99, Visits: 495
I have read the article and played with your test scripts and it makes sense. We do not do diffs so it will not affect us now but good to know for future use.

Although I did not know that it is possible to do COPY_ONLY Log backups as well.

I did a test on this and it is possible to delete a log backup from the sequence of backups (if that log was backed up with COPY_ONLY) and not fail the restore -- it just skips on to the next one in sequence.

I then thought, well why not do all log backups using COPY_ONLY and then never have to worry about a lost or corrupt backup again!!!

I assume this text from MSDN answers that question:
"When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup,which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist."


Post #1429036
Posted Monday, March 11, 2013 3:09 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 @ 3:43 PM
Points: 43,047, Visits: 36,206
UncleBoris (3/10/2013)
I then thought, well why not do all log backups using COPY_ONLY and then never have to worry about a lost or corrupt backup again!!!


Because copy_only log backups don't truncate the transaction log and hence don't mark the space in the log as reusable. You take all your log backups with copy_only and things will work great for the short time until the log file grows enough to fill the drive.

The point is that a copy_only log backup is not part of the log backup sequence, hence you can delete it without problem. It's not that SQL allows you to skip to the next in sequence, it's that the one you deleted was never part of the sequence 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 #1429114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse