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.