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).

 

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).

 

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

 

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″]