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:
| 1 | 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.
Backing up to NUL:
When you’re doing backup speed testing, you can back up to DISK=’NUL:’ and SQL Server doesn’t actually write the backup file to disk – it just discards the data. This helps measure how fast SQL Server’s backup processes can read the data from disk and compress it on this particular server.
The trick here is to take backup on NUL device. It’s not a typo and there is no missing L in NUL. It is called as “NULL Device”. As per Wikipedia –
The null device is a device file that discards all data written to it, but reports that the write operation succeeded. Its represented by NUL: or NUL on DOS
Taking backup is NUL device is as good as taking backup and deleting it. Since the backup is taken to a device, SQL would send it to the operating system they way backup would have been sent and due to NUL device, operating system discards it and tells SQL that your data is written successfully. You can assume it as writing to directly to the recycling bin, which would be deleted once completely.
Coming to its innovative use. I have seen few DBAs having scheduled job to take backup of transaction log to the NUL device using below command.
BACKUP LOG ProductionDB TO DISK = 'NUL'
BACKUP LOG [MyDB] TO DISK='NUL' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
Comments