Blog Post

SQL Server – Backup to NUL trick - read throughput

,

A neat test for read speed backup throughput is to use the Backup to NUL .

NUL is a special “file”. It's as a nul device , anything  “written” to it is discarded.Don’t confuse with the SQL reserved word NULL.

Before you run this statement , note that even as the file is not going anywhere , SQL Server still considers it to be valid BACKUP – therefore any subsequent LOG backups would be invalid , until you commit another FULL BACKUP.

Use the COPY_ONLY  option to avoid this problem

   1:  backup database MY_DB to disk = 'NUL:' with stats = 10
Sample output:
Processed 1706648 pages for database 'MY_DB', file 'MY_DB' on file 1.
100 percent processed.
Processed 38 pages for database 'MY_DB', file 'MY_DB_log' on file 1.
BACKUP DATABASE successfully processed 1706686 pages in 28.221 seconds 
(495.417 MB/sec).

 

Accompany the statement with  : Logical Disk Read Bytes/sec on relevant disk  to obtain read throughput

 See Also

Copy only backup in SQL server 2005 and not break the backup chain

Disk IO performance , disk block size tuning and SQL Servers

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating