March 7, 2013 at 10:38 pm
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
March 7, 2013 at 11:05 pm
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[/url]
Learn Extended Events
March 7, 2013 at 11:45 pm
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.
March 8, 2013 at 12:27 am
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
March 8, 2013 at 3:12 am
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
March 8, 2013 at 8:52 am
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[/url]
Learn Extended Events
March 10, 2013 at 5:38 pm
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."
March 11, 2013 at 3:09 am
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
April 17, 2015 at 7:27 am
Nice answer regarding the Copy_Only Backups and Log Chain and Transaction Logs.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply