BACKUP DATABASE TO DISK = N‘NUL’ – and misconceptions

Hopefully this will help someone out there.

There is a command, BACKUP DATBASE <db_name> TO DISK = N’NUL’ and apparently people either don’t know that is there, or are using it for all the wrong purposes.

We use this particular command to find out our “golden point”, so to speak, which means that we can find out the maximum throughput when taking a backup.

To give you an example:

BACKUP
DATABASE [test] TO DISK = N’K:\FullBackups\test’
WITH  COPY_ONLY, NOFORMAT, INIT,
NAME =  N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD
GO

This gives me, among other things:

BACKUP DATABASE successfully processed 1931558 pages in 232.509 seconds (63.122 MB/sec).

Performing the same script as above, but with ‘NUL’:

BACKUP
DATABASE [test] TO DISK = N’NUL’
WITH COPY_ONLY, NOFORMAT, INIT,
NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD
GO

And the result:

BACKUP DATABASE successfully processed 1931206 pages in 103.030 seconds (146.438 MB/sec)

So you can clearly see that I can actually achieve a higher throughput, namely shorten the time it takes to backup up my database.

Okay, now that you know what it does, let’s clear some misconceptions about its use:

• I saw people using it to clear the DCM pages (basically to make their differential backups smaller) – for this one to be achieved, WITH COPY_ONLY will be removed, of course, since this command preserves the LSN chain intact.
  Why on Earth would you want to do it like this to have smaller differential backups? I thought that backup management relates to full, differential and transaction log backups.
• I heard people using the same backup device (‘NUL’) to clear their transaction logs – huh? WTF…

To further develop on the two above: as far as SQL Server is concerned, a backup to ‘NUL’ is just that, a backup. It means that the actual write happened as far as SQL Server is concerned. So when performing a classic restore, you need the last full backup, the last differential backup and all (or some) of the transaction log backups.
But since you wrote to ‘NUL’, there’s no physical presence of the last full backup, so you cannot restore from it. The same goes with the transaction log backups.

• I saw people doing BACKUP DATABASE TO DISK = N’NUL’ to test their throughput. And they forgot to put WITH COPY_ONLY. Guess what happened:-).

Ok, so basically this is a useful tool for measuring throughput, but use it wisely. Also, as I just discovered half an hour ago, it works perfectly on my laptop, but not on production, where I have all kinds of activity on the server.

BACKUP DATABASE successfully processed 1931206 pages in 103.030 seconds (146.438 MB/sec)– backup to ‘NUL’ device
BACKUP DATABASE successfully processed 1931558 pages in 92.509 seconds (163.122 MB/sec) – COPY_ONLY backup performed to physical disk (on SAN)

So my “golden spot” is far below the actual speed of the backup. Makes you wonder, isn’t it? J

I hope this helps.

Best regards,
io