SQL Server (2005/2008): Does full backup truncate the log in full recovery mode
Solution 1:
Nope - it definitely doesn't. The only thing that allows the log to clear/truncate in the FULL or BULK_LOGGED recovery models is a log backup - no exceptions. I had this argument a while back and posted a long and detailed blog post with an explanation and a script that you can use to prove it to yourself at Misconceptions around the log and log backups: how to convince yourself.
Feel free to follow up with more questions. Btw - also see the long article I wrote for TechNet Magazine on Understanding Logging and Recovery in SQL Server.
Thanks
Solution 2:
A full backup does NOT truncate the log, you must perform a backup log operation. A full backup does NOT re-set the log chain -- that would totally screw up replication/log shipping, etc.
You'd have to look closely at how SQL Server does backups but know that in-flight/long running transactions are not included in the backup (otherwise the backup may never complete) so it's not quite accurate to say that a full backup of an online-database is guaranteed to make the next log backup obsolete.
http://msdn.microsoft.com/en-us/library/ms175477.aspx
Solution 3:
From my understanding the only thing that truncates the transaction log is a log backup.
A full backup only copies enough of the log so that it is transactionally consistent, because it takes a while for the backup operation to complete & in that time, the pages copied may have changed.
You still need your log backups for point in time recovery.
I don't have MSDN to link to, but i can link you to Paul Randal's blog, who was a developer on the SQL Server team, wrote DBCC CHECKDB and parts of Books Online.
He also answers questions on this forum, so that would be an even better authority then 2nd/3rd hand information from me :)
Solution 4:
People often have a misconception about the full backup and log backups. In order for the backup to work in FULL backup recovery model, the t-logs must be used, as during the backups there may still be transactions going on in the database (unless you perform a so called COLD backup when you shut down the database). Oracle uses the same concept when you have a database in ARCHIVELOG mode. The sequence of a backup boils down to this:
- Start backup - suspend all actions in real files and write to t-logs.
- Perform backup - all transactions continue, but are not written to real files, they are written into t-logs
- End backup - resume writing database transactions to real files.
- If necessary flush what's in the T-logs into the real files.
That is the reason why t-logs are not by default truncated/shrinked, as they are a vital part of transaction continuation during the backup phase.