Break log chain
-
Hi All,
Quick question - I have a client that uses their preferred DB backup 3rd party software that takes an age to do a full DB backup (11-12 hours for 300Gb DB). There are transaction log backups going on every 15 mins by the same software but apparently this takes a lot less time (as you would expect). I raised a concern as I thought that if the system failed and went down in those 12 hours that it takes to do the backup that the earliest that you could restore to would be a bit more than 12 hours prior (T-1 Full backup + every trans log backup to up to the full backup). However their DBA seems to think that they could use the trans log backups past the beginning of the failed last backup i.e. T-1 Full backup + every trans log backup up to point of failure. Wouldn't the last full backup break the log chain and mean that all the trans log backups in those 12 hours would be redundant?
Thanks in advance
Elliot
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk -
Your client is right. A full database backup does not break the chain of transaction log backups. They'll be able to use the last good full database backup, and restore all subsequent transaction log backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
I concur with Ray. Full and Differential backups do not break the log chain.
-
The only things that truncate the transaction log are log backups, explicit truncations (Backup log ... truncate only) and a switch to simple recovery. Neither full nor diff backups affect the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks to you all for your replies. A confused brain is now slightly less confused.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk -
A FULL datbase backup do not break the log chain.if you switch from FULL or BULK Logged model to SIMPLE recovery modle breaks the log chain.
use this link to understand further,
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
-
Yes it's true, I've had it mentioned to me several times by an MCM that as of 2005 transaction logs will not break the log chain. Nice feature.
Hope this helps,
Rich[p]
[/p] -
RichardDouglas (7/28/2010)
Yes it's true, I've had it mentioned to me several times by an MCM that as of 2005 transaction logs will not break the log chain. Nice feature.Transaction log backups or full backups? Transaction log backups do truncate the log after backing it up. That's why space is reusable after running a log backup. The 'full DB don't truncate log' is not a 2005 feature. SQL 2000 behaved that way, probably 7 too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sorry it was late and my last post wasn't very clear.
Assume we have the following fictional backup strategy going on:
The database in FULL recovery mode.
Daily Full backups at 00:00
Daily differential backup at 12:00
Hourly transaction log backups at 30 mins past the hour.
If the full backup could not be verified on Tuesday at 00:00 and the database required a restore at 1pm that day the administrator would restore the database from Monday at 00:00, then the Monday diff at 12:00, then the remainder of the transaction logs up until the piont of failure (we'll skip tail log backups in this fictional scenario).
The reason for this is that the transaction log is no longer linked to a particular full backup, however the differential backup is linked to the last full backup. Hence having to restore the diff from the previous day prior to restoring all available transaction logs since the diff was taken.
Rich
Hope this helps,
Rich[p]
[/p] -
RichardDouglas (7/29/2010)
The reason for this is that the transaction log is no longer linked to a particular full backupThey never have been. Transaction log backups are linked to the previous transaction log backup only. This is not new in 2005. 2000 behaved the same way. 7 as well I think. I have no experience of the earlier editions.
The only time that a log backup is based on a full is when the full backup is the first since creating DB/switching to full/bulk-logged recovery and that's the first log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The only way to truly tell if you can do a point in time restore, is to actually do a point in time restore (preferably to some other server), with known changes before and after that point in time. See what actually happens.
This is doubly true with third party backup utilities. Perhaps it does a deliberate log truncation somewhere, perhaps the backups it takes are unusable for some other reason, perhaps everything is fine.
Essentially: if the theory says it won't work, assume it won't work. If the theory says it will work, see what happens in practice.
-
Read this QotD
http://www.sqlservercentral.com/Questions/Tags/Backup/Backup/
and related discussion will clear all your confusion.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply