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 write speed for your disk! I have included the code snippet below if you want to play around with it!

--take FIRST full backup of AdventurWorks2012
BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\backups\AW_FULLBackup_1.bak'

--take FIRST LOG backup of AdventureWorks2012
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\backups\AW_LOG_Backup_1.trn'

--take NUL: LOG backup of AdventureWorks2012 <---THIS WILL BREAK LOG CHAIN
BACKUP LOG AdventureWorks2012 TO DISK = 'NUL:'
--WITH COPY_ONLY

--take 3rd LOG backup of AdventureWorks2012
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\backups\AW_LOG_Backup_2.trn'

--DROP AW Database
DROP DATABASE AdventureWorks2012

--RESTORE from FIRST FULL
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\backups\AW_FULLBackup_1.bak'
WITH NORECOVERY

--RESTORE FIRST LOG
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\backups\AW_LOG_Backup_1.trn'
WITH NORECOVERY

--RESTORE SECOND LOG (will fail)
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\backups\AW_LOG_Backup_2.trn'
WITH RECOVERY

Leave a Reply

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

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