Published on

October 16, 2015

Taking Backup on NUL Device in SQL Server

Have you ever wondered if there is a way to take a backup in SQL Server without actually consuming disk space? Well, there is a trick that allows you to do just that. However, I must warn you that this trick is very dangerous and should never be used in a production environment. Today, I want to share this trick with you and discuss its potential use cases.

The trick involves taking a backup on a device called the “NULL Device”. The NULL Device is a special device file that discards all data written to it, but reports that the write operation succeeded. In SQL Server, the NULL Device is represented by NUL: or NUL on DOS.

So, how does taking a backup on the NULL Device work? When you take a backup on the NULL Device, SQL Server sends the backup to the operating system as it would with any other backup. However, since the NULL Device discards all data, the operating system simply discards the backup and tells SQL Server that the data has been written successfully. It’s like writing directly to the recycling bin, which gets deleted once it’s full.

Now, let’s talk about an interesting use case for this trick. I’ve come across a few DBAs who have scheduled jobs to take a backup of the transaction log to the NULL Device using the following command:

BACKUP LOG ProductionDB TO DISK = 'NUL'

When I asked one of them why they were doing this, they gave me a fascinating answer. Before moving to SQL Server 2012 AlwaysOn Availability Groups, they had a database that was always in the “simple” recovery model. To put the database in an Availability Group, it must be in the “full” recovery model. However, they didn’t actually need transaction log backups and were okay with just the last full backup, which they took once daily. They only needed a secondary replica for reporting purposes. So, they started taking the transaction log backup to the NULL Device to avoid consuming disk space unnecessarily.

On a lighter note, the DBA suggested that Microsoft should introduce another recovery model, such as “Simple-AvailabilityGroup”, which would function the same as the “simple” recovery model but discard log records after they have been applied to all secondary replicas.

It’s important to note that using the NULL Device for backups can disrupt the disaster recovery process. Although the SQL Server logs may show that backups have been taken, they are not actually present anywhere. Therefore, extreme caution should be exercised when using this command.

Here is an example of the message that appears in the SQL Server ERRORLOG when a backup is taken on the NULL Device:

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.

While this trick can be useful in certain scenarios, it is crucial to understand the risks and limitations associated with it. Always evaluate the impact on your disaster recovery strategy before implementing such techniques.

Thank you for reading! I hope you found this article informative and thought-provoking. If you have any questions or comments, please feel free to share them below.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.