Backing up to NUL:

Backing up to NUL is like backing up directly to the trash

Linux folks have lots of jokes & fun t-shirts about /dev/null/. The null device is a handy location to dump output to, when you don’t actually care about the output. In PowerShell, you can use Out-Null to dump extra output into the ether, and keep your logs or console clean.

Just like Linux has /dev/null/, Windows has NUL:

This means you can back up your database into the ether: BACKUP DATABASE MyDatabase TO DISK = 'NUL:'

That seems like a terrible idea!

It certainly isn’t going to help you if you need to restore. But it can be pretty helpful.

If you’re investigating slow backups, this can be a great tool to help in troubleshooting. By backing up to NUL, you can eliminate latency introduced by network & your backup storage. This effectively tells you if SQL Server is slow taking backups, or if the slowness is on the other side.

If you’re using Availability Groups, you need to take a full backup before you can add it to the AG. Especially for non-production environments, when I add a new database to an AG, I’ll back it up to NUL, then use direct seeding to initialize the database into the AG. It’ll get a real backup tomorrow, and that’s good enough for non-production databases.

Use caution

If you find yourself backing up to NUL often, question yourself about why.

If you’re doing it in Production, make sure you understand how it impacts your recovery plan.

Log backups to NUL will break log shipping, and break your log chain for point-in-time recovery. Full backups to NUL will still reset the differential bitmap (unless you use COPY_ONLY).

It can be a handy trick, but it can also be really dangerous. Proceed with caution.

Be the first to comment

Leave a Reply