Yes, I spelled that correctly. NUL not NULL. NUL is basically a location you can send a backup to. In fact, one of the awesome bonuses of this location is that your backup will take up exactly 0 bytes of space. You got it, absolutely none! Now before you get too excited (and some of you did didn’t you), NUL is the bit bucket. It goes absolutely nowhere.

Yes if you take a backup like this:

BACKUP DATABASE Test TO DISK = 'NUL'

Then as far as SQL Server is concerned you took a backup, but the backup file is never actually created.

So why would you ever want to do that? SQL thinks you took a backup, but you have nothing to recover from. Sounds a bit, well, stupid, doesn’t it? Well, there are a few reasons.

• You are testing your backup speed but want to ignore the write IO.
• You have a test database in FULL recovery and you’ve never taken a log backup. The database is ~50mb and the log is ~600gb (if those sizes seem oddly specific .. well .. yeah) and you don’t have room to take that huge log backup or a full one for that matter. You need to clear out the log, get it shrunk and move on (with regularly scheduled log backups hopefully).
• You have a database that has to be in FULL recovery (It’s an AG primary for example) but you don’t care about point in time recovery, and you don’t want to store the log backup files.

There are lot’s of possibilities here, although remember that all of them are exceptions to the rule. When you backup to NUL you do not have a way to recover your database! This absolutely should not be done lightly and without knowing why you are doing it.

Now in case you are wondering where NUL comes from I was reading a post by the great Gail Shaw (b/t) the other day about the difference between NUL and TRUNCATE_ONLY when taking a backup and she reminded me it’s from the good old DOS days. A virtual device similar to COM1, LPT1 etc, that points to the bit bucket. FYI Gail’s post is a good read (as always) and you should take a minute to take a look at it.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, microsoft sql server