Backup to NUL


June 5, 2017 by Kenneth Fisher

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:


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.

9 thoughts on “Backup to NUL

  1. notarian says:

    This why I requested MinionBackup to send output to LPT1 and Sean uses that as the example of the worst requested feature.

  2. […] Kenneth Fisher shows how to use the NUL destination to test max potential backup speed: […]

  3. Great post! For those of us that have been around a while (since 1976) it’s good to see the old tricks still can get used.

  4. Grover Vivek says:

    Good Trick

  5. […] 1. Backup to NUL | SQL Studies […]

  6. […] 1. Backup to NUL | SQL Studies […]

  7. […] until you’ve done a full backup regardless of what type of backup was restored. According to Backup to NUL, you can backup to a NUL device to satisfy this requirement. This would be the fastest possible […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: