Backup to NUL

6

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:

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.

6 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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,674 other followers

Follow me on Twitter

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