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.
I’ve blogged about why backup log with truncate_only is like a bear trap, and I meant it: you shouldn’t be running this command except during extreme emergencies. When you truncate transaction logs, you lose the ability to recover to a specific point in time.
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.
Microsoft recognized the problems with TRUNCATE_ONLY and removed this capability completely from SQL Server 2008. If you try to use this command:
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
You get an error:
?Msg 155, Level 15, State 1, Line 1 'TRUNCATE_ONLY' is not a recognized BACKUP option.
The only workaround in SQL Server 2008 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 full.
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.
Method 1: How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008
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:
BACKUP LOG MyDb TO DISK=’NUL:’
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.
Method 2: Use Quest LiteSpeed to Fake a Log Backup
If you don’t want to switch into simple recovery mode, and if you’re using Quest LiteSpeed for SQL Server, there’s a back door workaround.
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.
Open a command prompt, go to the folder where the LiteSpeed engine is installed (typically \Program Files\Quest Software\LiteSpeed\Engine) and type:
sqllitespeed.exe -B log -D AdventureWorks -F e:\test.bak --nowrite
The parameters are:
- -B log = the type of backup we’re doing, database or log.
- -D AdventureWorks = the name of the database we’re backing up
- -F e:\test.bak = the name of the file we’re backing up to, except that…
- –nowrite = don’t actually write the file.
LiteSpeed will make SQL Server think it’s doing a backup, but since LiteSpeed controls what gets written to disk, it just smiles and nods while SQL Server keeps shoving information across. “Uh-huh, yeah, I got it. Keep that data comin’.”
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. Well, heck, it’s not even my knowledge – big thanks to David Gugick and James Deen of Quest Software for pointing out this trick.
P.S.: yes, I shouldn’t have even written this technique in public, but there’s a lesson here. We had some controversy earlier this week about shrinking databases, and I wanted to show how you can convey dangerous information with proper warning labels. If you don’t explain this stuff, someone can stumble across your instructions in a web search, think it’s completely okay, and do it without understanding the consequences.
And before you shrink your logs, read my thoughts on that too.
Brent Ozar PLF Team
Brent Ozar PLF is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop solutions that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.
Follow Me:
