Backing Up Databases to Nul?

June 16, 2021 ยท Matthew

This is pretty obscure, but did you know you can backup your databases to nul? Well you can, just like this:

BACKUP DATABASE [MyDatabase]
TO DISK = 'nul';
BACKUP LOG [MyDatabase]
TO DISK = 'nul';

This performs a backup just like if you were backing up to a file location, it just doesn’t actually backup data anywhere. Many of the same parameters can even be enabled such as backup compression for one, but why would you want to backup to nowhere? I can think of two common situations where this might be useful.

Non-Production Databases Which Require FULL RECOVERY

Non-Production databases often are set to simple recovery because there is not a need minimize data loss and there is no need to rely on backups the same way as in production environments. However, there are instances when non-production databases must be set to full recovery, such as when the databases exist in an Always-On Availability Group. If you never backup the transaction log of these databases logs will continue to grow until there is no space left for it to grow but in a non-production environment the cost of storing unneeded backups is a waste of storage space and a waste of I/O.

Initializing the LSN Chain

SQL Server 2016 improved Always-On Availability Groups with the option of automatically seeding the replicas. Earlier versions required you to first make a backup of the database and restore that on the replica servers with NORECOVERY and then the replica could be added to the availability group. Automatic seeding avoids the manual backup/restore steps and performs them automatically as soon as the primary replica is joined to the availability group. There’s a gotcha though; in order to join the database there needs to be a clean backup chain so that SQL Server can synchronize the replica databases. That backup chain begins when a full database backup is made.


Leave a Reply

Your email address will not be published. Required fields are marked *