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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » How can you issue a full backup and not...

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

Rate Topic

Display Mode

Topic Options

Author

Message

UncleBoris

Posted Thursday, March 07, 2013 10:38 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 70, Visits: 316

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

SQLRNNR

Posted Thursday, March 07, 2013 11:05 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

Lynn Pettis

Posted Thursday, March 07, 2013 11:45 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 21,600, Visits: 27,422

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)

Post #1428417

UncleBoris

Posted Friday, March 08, 2013 12:27 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 70, Visits: 316

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

GilaMonster

Posted Friday, March 08, 2013 3:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

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

SQLRNNR

Posted Friday, March 08, 2013 8:52 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

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?

Great write-up on it Gail.

Post #1428621

UncleBoris

Posted Sunday, March 10, 2013 5:38 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 70, Visits: 316

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

GilaMonster

Posted Monday, March 11, 2013 3:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

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 »

Permissions