April 6, 2016 at 4:18 am
Hi all,
It seems that during full backup does include the transaction logs
Can I confirm that
a) is transaction log (eligible for or/& being truncated) after full backup ?
b) incremental backup does not include transaction log, and thus does not cause log truncation.
Regards,
Noob
April 6, 2016 at 4:25 am
A full backup does not truncate the transaction log.
There's no such thing as an incremental backup in this case. Do you mean differential? Transaction log?
April 6, 2016 at 4:34 am
szejiekoh (4/6/2016)
a) is transaction log (eligible for or/& being truncated) after full backup ?
Full backups do not ever truncate the transaction log
b) incremental backup does not include transaction log, and thus does not cause log truncation.
No such thing as an incremental backup.
If you mean differential, it includes log just like a full backup does and does not truncate the log, just like a full 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 recoverability
April 7, 2016 at 6:54 am
GilaMonster (4/6/2016)
szejiekoh (4/6/2016)
a) is transaction log (eligible for or/& being truncated) after full backup ?Full backups do not ever truncate the transaction log
b) incremental backup does not include transaction log, and thus does not cause log truncation.
No such thing as an incremental backup.
If you mean differential, it includes log just like a full backup does and does not truncate the log, just like a full backup.
Hi Gila and all,
Thanks for the reply.
q1) Can I know the rational/reason behind on why full backup with transaction log will not allow the truncation of the transaction log ? (-- at least on the part of the transaction log that is already being backup) ?
Regards,
Noob
April 7, 2016 at 7:15 am
szejiekoh (4/7/2016)
q1) Can I know the rational/reason behind on why full backup with transaction log will not allow the truncation of the transaction log ? (-- at least on the part of the transaction log that is already being backup) ?
That choice ensures that all transaction log backups form an unbroken chain. Suppose you need to restore and find that the last full backup is unreadable, you can still restore from the previous full and then apply all tran log backups. That would not be possible if log records included in the full were missing from the chain of tran log backups.
April 7, 2016 at 7:21 am
So that full backups don't mess with the log chain.
And the full backup doesn't back up the entire transaction log. Just what it need to restore consistent. So if the log contains 4 hours of log records, a log backup will backup all 4 hours, a full backup might just back up the last 5 minutes because that's all it needs.
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 7, 2016 at 7:23 am
Hugo Kornelis (4/7/2016)
Suppose you need to restore and find that the last full backup is unreadable, you can still restore from the previous full and then apply all tran log backups. That would not be possible if log records included in the full were missing from the chain of tran log backups.
Also, if it did and someone took an ad-hoc full backup, that would mean that the DB could no longer be restored from the scheduled backups. That's a disaster waiting to happen.
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 7, 2016 at 8:16 am
If the behavior of this is not what you expected and you don't need the ability to restore to a point in time, consider using the Simple recovery model instead of Full.
April 11, 2016 at 9:22 am
Hi Gila, Hugo, and everyone,
Thanks for showing me the light, I get the reason now !
Sorry for the slow reply!
Regards,
Noob
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply