I've just read through a lot of MSDN documentation and I think I understand the different recovery models and the concept of a backup chain. I still have one question:
Does a full database backup truncate the transaction log (using full recovery mode)?
-
If yes: Where is this mentioned in the MSDN? All I could find was that only BACKUP LOG truncates the log.
-
If no: Why? Since a full database backup starts a new backup chain, what's the point in keeping the transactions that were finshed before the full backup active in the log?
Best Answer
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