How to Know Backup Speed in SQL Server? – Interview Question of the Week #277

Question: Where does SQL Server report a backup’s speed? Read the completion message in SSMS’s Messages tab. It gives the processed pages, elapsed seconds and reported MB/sec.

The original AdventureWorks2017 example makes the answer easy to see. For a one-off test, use a dedicated filename and a copy-only backup so the test doesn’t change the differential base of your regular backup strategy.

-- Use your sample database and a new, dedicated backup filename.
BACKUP DATABASE [AdventureWorks2017]
TO DISK = N'D:\data\AdventureWorks2017_speed_test.bak'
WITH COPY_ONLY, CHECKSUM;

Change the database and path to your lab’s values. The SQL Server service needs access to the destination. This example does not use INIT or FORMAT to overwrite existing backup sets.

That message is a backup-operation throughput measure, not an isolated benchmark of the destination disk. Source reads, CPU, compression, checksums, storage and other workload can all affect elapsed time. With compression, the reported processing rate and the number of bytes written to the backup file are different measurements.

Compare like with like: the same database, backup type and options, similar workload, and the same destination. A tiny backup can spend much of its elapsed time on fixed overhead. A change in one run doesn’t by itself identify a bottleneck.

Schedule for recovery needs

Quieter periods can reduce contention for a large full backup. That doesn’t mean log backups should wait for an idle server. Their frequency must meet the business’s recovery requirements, and successful restore tests matter more than a pleasing MB/sec number.

Related posts cover the backup timeline, the restore timeline interview question, seven backup reminders, last full backup time and size, restoring with SSMS, monitoring backups remotely and a database remaining in RESTORING.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.