BACKUP DATABASE testDB TO DISK=’NUL:’

A very quick and easy way to see the how fast your hard disk is, is to run:

BACKUP DATABASE testDB TO DISK=’NUL:’

Don’t mistake the ‘NUL’ for NULL. They are two separate things. The ‘NUL:’ is considered a device and anything written to ‘NUL’ is discarded.

I downloaded and installed the StackOverflow database on my laptop (if you want to install the StackOverflow database on your machine to play around, check out Brent Ozar’s instructions on how to do it here). My laptop is running Crucial MX200 1TB SSD. My throughput is 490.491 MB / sec (see snapshot below).

backup-database-to-nul

 

Caveats

SQL Server will treat the ‘NUL:’ LOG backup statement as a valid backup. Meaning, if you have a previous LOG backup and then run ‘NUL:’ backup it will break your LOG backup chain and you will get Msg 4305, Level 16, State 1, Line 1 error as well as Msg 3013, Level 16, State 1, Line 1 error (see screenshot below).

restore-from-nul-fails

 

You can get around this by using the COPY_ONLY option (see screenshot below)

restore-from-nul-with-copyonly-1

 

That way it doesn’t affect the backup chain and you still get the read speed for your disk! I have included the code snippet below if you want to play around with it!

[snippet id=”40″]

3 Replies to “BACKUP DATABASE testDB TO DISK=’NUL:’”

  1. Hi Mohammad
    aren’t we testing the disk’s read speed rather than its write speed, since nothing is ever written to disk?
    Kind regards
    Thierry

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.