Backup to NUL
14June 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.
SQL Studies 
This why I requested MinionBackup to send output to LPT1 and Sean uses that as the example of the worst requested feature.
[…] Kenneth Fisher shows how to use the NUL destination to test max potential backup speed: […]
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.
Thanks 🙂 I thought it was kind of a cool trick.
Good Trick
Thanks. It’s not something you will use often but handy when you need it.
[…] 1. Backup to NUL | SQL Studies […]
[…] 1. Backup to NUL | SQL Studies […]
[…] 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 […]
We have NUL backup configure of multiple databases, I wanted to remove that NUL backup from databases but when I am removing the backup it is giving me following error.
===================================
Backup failed for Server ‘Server Name’. (Microsoft.SqlServer.SmoExtended)
——————————
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48044.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
——————————
Program Location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropBackupOptions.OnRunNow(Object sender)
===================================
To accomplish this action, set property Devices. (Microsoft.SqlServer.SmoExtended)
——————————
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48044.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyNotSetExceptionText&LinkId=20476
——————————
Program Location:
at Microsoft.SqlServer.Management.Smo.Backup.Script(Server targetServer)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
What is the command you are using? Or are you using a maintenance plan or something?
I have disabled the maintenance plans for all databases and then I am trying to remove the NUL: backup by going to databases right click “Back Up” and select NUL: backup path and click remove then press OK and got the error I have pasted above.
You have to specify a location for a backup, it can’t just be blank, that’s what the NUL is for. To act as kind of a blank.
[…] the fact that I took the backup to NUL. That was just me being lazy for the demo. You can try it to a different location if you want, it […]