SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)


Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...