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