Log Shipping – NORECOVERY vs. STANDBY mode

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.

What are the impact of this Backup location

Anything that’s written to NUL is discarded. So when a transaction log backup is taken at ‘NUL’ , you are actually breaking the transaction log chain. That means, you wont be able to restore the transaction logs. As 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. 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.

Warning!
This command runs great accept under the following conditions:

1. If you run a differential backup, don’t run this command! The reason is that the differential backup picks up pages that have changed. Running the backup command resets the flag on the page. Since the backup to a NUL: device reads the page, but does not back it up to media, the flag is reset as if it HAD been backed up… so the database does NOT back it up during the next differential. If you have run a backup to NUL: and do differential backups, run a FULL backup immediately and everything will be fine.
2. If you have heavy disk IO now, a backup to a NUL: device will only add more stress slowing things down more.
3. For reason #2, run this command in off hours.