SQL SERVER – Taking Backup Without Consuming Disk Space

来源:互联网 时间:1970-01-01

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

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.

<span>BACKUP </span><span>LOG </span><span>ProductionDB </span><span>TO DISK = </span><span>'NUL'</span>

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 ( http://blog.SQLAuthority.com )