BACKUP LOG WITH TRUNCATE_ONLY is no longer a recognized BACKUP option. This command get rid of the contents of your SQL Server’s transaction log without really backing it up. This command will to invalidate the point in time recoverability of the database as this breaks the log chain.
This command is used by Database administrators when they end in situations where getting rid of the transaction log is only way by which he can shrink their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.
Microsoft always recommends that DBA’s must switch to simple recovery mode getting rid of the transaction logs instead of truncating logs by above command if no Point in Time recovery is required. But by using simple recovery mode, there is no scope of point in time recovery which means in case of disaster, all you can get is last Full Backup to be restored which may be a day or two old.
TRUNCATE_ONLY option is capability is completely removed in from SQL Server 2008. The error encountered if you try use this option is
Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.
There are two ways of getting rid of transaction logs in case of extreme emergencies in SQL Server 2008:
1.Backing up Transaction logs by sending Log Backups to “NUL:” but it should only be in case of extreme emergencies.
BACKUP LOG [Demo1] TO DISK = N'Nul:' WITH NOFORMAT, NOINIT, NAME = N'Demo1-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
2.Switching database to Simple and Back to Full Recovery mode can also be used under extreme emergencies where T-Log Backup is not possible due Space constraints or due to size.
Note: One should always do a fresh FULL backup after using any of the methods above where log chain is broken, this enables
Thanks for sharing this informative blog with us.