How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012
BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server’s transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.
Why ‘truncate_only’ is not a recognized backup option.
When you truncate transaction logs, you lose the ability to recover to a specific point in time. You shouldn’t be running this command except during extreme emergencies. Unfortunately, administrators started running it on a regularly scheduled basis, and then they got surprised when they couldn’t restore the way they wanted.
Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log. This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup. You could lose hours – maybe even days – of data.
To stop people from shooting themselves in the foot, Microsoft removed this capability completely from SQL Server 2008. If you try to use this command:
You get an error:
The only official workaround in SQL Server 2008 and newer is to switch the database’s recovery model to simple as shown in Books Online . This empties out the transaction log, thereby letting the DBA run a DBCC SHRINKFILE afterwards, then switch the recovery model back to
That solution still suffers from most of the same problems as using TRUNCATE_ONLY – the database’s recoverability is compromised. It’s just as bad of a solution, but unfortunately Microsoft can’t remove that workaround since we do need to put databases into simple recovery mode for other reasons.
How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008
Don’t try what you’re about to see at home. We’re what you call experts. We’ve got years of experience that keeps us safe. Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability. This should only be done in cases where a log has grown out of control and must be erased or else the system may crash. In any other situation, you should consider backing up the log with conventional means.
We can fake it by not writing our backup to a real device. SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere:
Remember, we’re still not fixing anything here: whatever caused the log file to grow in the first place can happen again and put us right back where we started.
Your data is not actually backed up. This is a giant problem. Don’t leave this script lying around where a junior DBA might see it and reuse it for regular backups. This should only be used to impress your friends with your useless knowledge of SQL Server, much like I’m doing here.
Other Common Questions About Transaction Log Backups
Q: Why shouldn’t I shrink log files?
Want to Learn More About SQL Server?Source: www.brentozar.com