Full vs Transaction Log Backup

Question

Full vs Transaction Log Backup

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
August 20, 2012 at 9:28 am

#262967

Hi,
I seem to have caught amnesia.
I understand that frequent transaction log backups help maintain the size of a transaction log.
However do Full backups help anything with the transaction log. Or does it simply just restore the transaction log as one big file if you don't run any tran log backups previously.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

smallmoney (8/20/2012)
Hi,
I seem to have caught amnesia.
I understand that frequent transaction log backups help maintain the size of a transaction log.
However do Full backups help anything with the transaction log. Or does it simply just restore the transaction log as one big file if you don't run any tran log backups previously.
Thanks

Full backups do not truncate (mark as reusable) any VLFs (Virtual Log Files). It backups up enough of the t-log to provide a consistent database as of the end of the backup. If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Full backups do not affect the transaction log.

Please read through this - Managing Transaction Logs[/url]

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Lynn Pettis (8/20/2012)
If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.

It would only backup the 21 GB if there's an open transaction or unreplicated statement at the beginning of the 21 GB.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Just a side note. Be careful about how you think about log backups. They're not done to maintain the log size or shrink logs or anything like they. Log backups are only a part of the point in time recovery process. Nothing else. If all you're concerned with is log size, just use simple recovery (not recommending that from a business & DR stand point mind you, just that if you don't care about business & DR, it's a lot easier).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

GilaMonster (8/20/2012)
Lynn Pettis (8/20/2012)
If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.
It would only backup the 21 GB if there's an open transaction or unreplicated statement at the beginning of the 21 GB.

True, guess I really should have stayed in bed longer. But I do know that if your t-log file has grown from say 8 GB (normal size) to 30 GB (due to activity) that even if there were no active transactions the t-log file restored will be 30 GB in physical size. This killed me several times during restores to a space limited server until I found out that the other DBA was rebuilding every index on every table in that database every night. I fixed that quickly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yup, the DB will be recreated exactly as it was at the time of backup. Caught me too, had a 50GB backup file that needed 120GB to restore (empty space in DB, empty space in log)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

GilaMonster (8/20/2012)
Yup, the DB will be recreated exactly as it was at the time of backup. Caught me too, had a 50GB backup file that needed 120GB to restore (empty space in DB, empty space in log)

Oh - the horror stories. Once had someone send me a backup that was a couple of gigabytes. When I went to restore - it would not because the actual data file was about 2GB and the transaction log needed 900GB.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

Transaction log maintenance can be a drag. The stairway about this topic is great.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001