October 21, 2008 at 10:17 am
only a little! We are still talking about tlogs and data in them and them filling up.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 23, 2008 at 8:05 am
SQLBOT (10/17/2008)
Here is what happens on a full database backup
1. lock database blocking all transactions
2. place a mark in the tran log
3. release the database lock
4. back up all data pages in the database
5. lock database, mark transaction log, unlock database
6. write all the transactions between the two marks to the full backup file (but they also stay in the log file)
Point 6 is wrong. A full backup will always store the active portion of the transaction log (up to the point the backup of the data pages ended), not just the transactions that occurred between the time the backup of the data pages started and ended.
Think about it. A full backup needs to know which transactions have/have not been committed. To do that, it needs to back up everything in the transaction log starting from the oldest active transaction, which may or may not have started before the backup began. This is done so that the roll-forward and roll-back processes may be performed correctly when the full backup is restored and the database is recovered.
To answer the OP's question:
Is it true that only the active portion of the log file included in the backup file. Say the log file size (doo.ldf) is around 100GB and the active log is 19GB so only 19GB will the part of the full back(.bak)
Yes, only the active portion of the transaction log is included in the backup.
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.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply