Or “It’s 10pm, do you know where your log records are?”
Something that I’ve started to see recently is the idea that Backup Log … With Truncate Only, which was deprecated in SQL 2005 and gone in SQL 2008 can be simulated by backing the log up to the NUL device (backup log … to disk = ‘Nul’). Is that true? If so, is that a good idea?
To answer those questions, first we need to explore what the two statements do.
Backup Log With Truncate_Only
When this statement is run against a database in full or bulk-logged recovery, SQL discards the inactive log records for that database. From that point until a full database backup is done, the database is in pseudo-simple recovery, the inactive log records get discarded when a checkpoint runs and any attempt to back the log up results in an error message.
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
So once the log has been truncated it’s exceedingly clear that the log chain is broken.
Backup Log to disk = ‘Nul’
To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL.
DOS, since the earliest days, has had a number of devices that can be treated like files, eg LPT1, COM1 and CON. NUL is another of those devices (virtual files, if you like) and is equivalent to the unix \dev\nul, a ‘file’ that deletes any data that’s written to it. The bitbucket, if you will.
So anything that’s written to NUL is discarded. So when a transaction log backup is made with ‘nul’ as the file destination, SQL dutifully reads over the inactive log records, formats them as for a transaction log backup and hands them off to the operating system which promptly discards the data and sends back an acknowledgement that the data has been written.
So SQL thinks that the log chain is intact. It discards the log records that were sent to Nul as it would after a normal log backup, because it thinks that they were backed up to disk. It does not switch the DB into a pseudo-simple recovery model. Inactive log records are not discarded at a checkpoint and future log backups will succeed.
That’s the main difference. Log records will still be retained after a backup to NUL. Log backups will succeed after a backup to NUL. They’re useless for restoring the DB because there’s a log backup missing, but they’ll succeed.
Replacement?
So, is backup to nul a replacement for backup with truncate_only? No, it’s not. The replacement for Backup Log with Truncate_Only is well documented. It’s switching the database to simple recovery. If the inactive log records in the log file do not need to be retained for recoverability, this is the way to tell SQL to discard them. If log backups need to be made after this, the DB can be switched back to full and a full DB backup taken.
Backup Log to Disk = ‘Nul’ is the same as backing up the log to a file and then deleting the backup file. Nothing more.
Excellent article and well written explanation Gail, I was not aware that disk = ‘Nul’ is in fact a pointer to a bitbucket of sorts.
Excellent article and clears up some misconceoptions about Nul. Thank you Gail
Best SQL blog entry I’ve read in quite sometime. Thanks!
Gail,
Thanks for the excellent article. Sometimes it’s hard to keep up on the new features as well as features that are being deprecated.
Thanks again for very intuitive advice!
I did a backup xxx to disk = ‘null’ and then searched my disk and found a file name with null in the sql folder. It seem the null file specified is being treated as a filename??
That’s null with 2 l’s. The NUL device has only 1.
Backup database master to disk = ‘null’ — creates a backup file named null
Backup database master to disk = ‘nul’ — backs up to the nul device, essentially deleting the backup
Pingback: Weekly Link Post 110 « Rhonda Tipton’s WebLog
Thanks Gail, so nul can be used to truncate the log file, i,e empty in active transactions in the log and there is no need to take a full backup after this operation??
I think you may want to read the post again, specifically the part that goes:
“Log backups will succeed after a backup to NUL. They’re useless for restoring the DB because there’s a log backup missing, but they’ll succeed.”
If you don’t want to retain log records for restore purposes, switch to simple recovery. You should only be in full if you’re retaining log backups for DR purposes and if that is the case, discarding a log backup (what this does) is WORSE than truncating the log because it’s only obvious what happened when you go to restore, which will likely be in a disaster situation
Thanks for the Article Gail. I have used nul few times knowing that actual data wont be written to any file but you explain it better. Also, I didnt know the difference between backing log to nul and using truncate_only clause. Thanks again for excellent explanation.
Backup Log to Disk = ‘Nul’ is kind of dangerous then. You may keep doing log backups after it only to discover that they are of no use. So ideally its use should be avoided then? if we switch from “full” to “simple”, atleast while doing a log backup, we’ll come to know that it is not possible and log chain is broken.
Yes, absolutely. It’s like making normal log backups to disk then deliberately deleting one.
The only real use for a backup to NUL is when tweaking and tuning backups for best throughput.
Well I have a client who don’t want his QA databases backed up, but wants to have them in full mode.
So this might do the trick for me, maintaining the DB healthy and log files small by taking backups to nul.
Was searching for a /dev/null and didn’t know about the nul file. 🙂
“The only real use for a backup to NUL is when tweaking and tuning backups for best throughput.”
Well you can imagine a scenario where your log backups have been failing anyway and you want to start over with your backup chain after addressing the original issue. You would back up log to disk = ‘nul’, possibly shrink the log file if it had grown to an unruly size, then run a full backup immediately. After this Subsequent log backups would be good.
Basically whenever you get in a situation where you want to continue to retain transactions in the log, but the current transactions are no good, moot, or otherwise unwanted, these are the circumstance where you would consider using backup to disk = ‘nul’. And it’s generally not the only way to get this accomplished – only the most convenient.
Hi Gail,
Can you please clear my question?
“””” So anything that’s written to NUL is discarded. So when a transaction log backup is made with ‘nul’ as the file destination, SQL dutifully reads over the inactive log records, formats them as for a transaction log backup and hands them off to the operating system which promptly discards the data and sends back an acknowledgement that the data has been written.””””
… SQL dutifully reads over the inactive log records….
What about active records? Will they too go to Null device?
As part of the log backup, yes, the entire log backup gets deleted. Active and inactive portions of the log are included in the log backups. Only the inactive portions are subsequently truncated of course.
It’s exactly the same as backing the log up to a file then deleting the file. SQL has no idea that the OS is discarding everything written.
dear mr. gail,
if only deleted log backup, how about the log itself ? still can’t reduce the size ? if yes, so what the point if only deleted the backup without resizing the log ? thanks.
Such an excellent article and what a myth about the backup log to nul command.