Thursday, September 22, 2011

BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008

If you are using SQL Server 2008, you will notice that BACKUP LOG WITH TRUNCATE_ONLY has been thrown out. There are several reasons Microsoft decided to cut this command loose. First of all, BACKUP LOG WITH TRUNCATE_ONLY is a misnomer, it doesn't backup the log(as the name suggests), it clears the contents(inactive portion) of the transaction log without backing it up, breaking the chain of your log backups and the ability to do point in time recovery. So how do we truncate the log in SQL Server 2008 then? Simple, change the recovery model to simple, use DBCC SHRINKFILE to shrink the log file and switch the recovery model back to full. This solution is far better than what was offered in SQL Server 2005. Remember that the purpose of full recovery model is to let us perform point in time recovery, but by doing a TRUNCATE_ONLY, you loose that ability. So if your business doesn't care about point in time recovery, switch to simple recovery model. The fundamental point am trying to emphasize here is that full recovery model and BACKUP LOG WITH TRUNCATE_ONLY don't go together, which means executing TRUNCATE_ONLY command defeats the purpose of setting the database to full recovery model. Many Developers/DBAs (inadvertently) used this command only to know later that they broke the continuity of their transaction log backups. Hence you are required to switch the recovery model to simple so that you know what you are exactly doing.

No comments:

Post a Comment