SQL SERVER – Taking Backup Without Consuming Disk Space

Long back, I learned this trick and found an interesting use of it. I totally understand that its very dangerous. Recently I have seen someone getting bitten by this so thought of sharing.

Warning: Don’t do this in any production environment

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

Solarwinds

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'

When I asked one of them about why they are doing it, I got a very interesting answer.

Before moving to SQL 2012 AlwaysOn Availability Groups, I had a database which has always been in “simple” recovery model.  To put it in an AG, it must be in “full” recovery model, and I know the reason also.  So due to full recovery mode, I am forced to take a transaction log backup and burn up disk space with transaction log backups. In reality, I don’t need them and I am OK with last full backup, which I take once daily. I need secondary replica for reporting purposes only. That’s the reason I am taking LOG backup to NUL device. On a lighter note, I believe that Microsoft should have another recovery model – for example, “Simple-AvailabilityGroup” – which would function the same as “simple” recovery model and discard log records after they have been applied to all secondary replicas. What do you think Pinal?

Here is the message in the ERRORLOG when backup is taken on NUL. Notice that disk path as ‘nul’

Database backed up. Database: master, creation date(time): 2015/10/15(05:53:51), pages dumped: 909, first LSN: 6795:16:84, last LSN: 6795:72:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘nul’}). This is an informational message only. No user action is required.

Hope it’s clear that this command would disturb the disaster recovery steps as you might assume that backups are taken as shown in SQL Server logs but they are not present anywhere. Please be very careful in using the command.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
Interview Question of the Week #042 – How Does SPACE Function Works in SQL Server?
Next Post
SQL SERVER – Script level upgrade for database ‘master’ failed – CREATE SCHEMA failed due to previous errors

Related Posts

6 Comments. Leave new

  • I can see that a level “Simple-AG” is needed. We are running alot of databases in simple mode to “simulate” a TempDB for batch reports. It´s because of some concurrent issues in our application then running multiple setup in same SQL instance.

    Reply
  • Hi Pinal,

    I was under impression that Microsoft has stopped supporting back up to be done on NUL device in latest release of SQL Server.

    Thanks for the info.

    Br,
    Anil

    Reply
  • Very cool, I’ve been using this trick for a while now ;) Thank you for sharing.

    Reply
  • When using log shipping, can this be used to “reset the DCM” after taking a copy-only full backup from the read only replica? Then subsequent differentials would be small again. Or is there actually an undocumented SQL command to reset the DCM?

    Reply
  • ildjarnisdead
    April 11, 2019 3:02 pm

    I use this to fake backups on an educational database which is restored to a base image every night. sp_Blitz was constantly complaining that there were no backups for this database, which technically is correct, but a backup wasn’t needed anyway. So I scheduled a NUL backup to trick sp_Blitz into thinking there was a backup.

    Reply
  • Its handy for that stupid full backup that is required just before adding to the AG, which in turn, just does another full backup. Redundant backups are redundant. :/

    Reply

Leave a Reply

Menu