SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can you issue a full backup and not interrupt the LSN's


How can you issue a full backup and not interrupt the LSN's

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 733
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33556 Visits: 18560
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

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

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)
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 733
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90929 Visits: 45284
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, 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


SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33556 Visits: 18560
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 733
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."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90929 Visits: 45284
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, 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


santosh_va
santosh_va
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
Nice answer regarding the Copy_Only Backups and Log Chain and Transaction Logs.

http://www.sqlservercentral.com/blogs/sqlinthewild/2011/03/08/full-backups_2C00_-the-log-chain-and-the-copy_5F00_only-option_2E00_/
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