Home / Databases / SQL Server

DatabasesHow-toIntermediate

Optimization: faster SQL Server backups and restores - compression, striping, buffer settings and backup to URL

Slow backups overrun maintenance windows, and slow restores stretch outages. Measure throughput, turn on compression, stripe to several files or URLs, tune BUFFERCOUNT and MAXTRANSFERSIZE, speed up restores with instant file initialization, and test restore time regularly against the RTO.

Deepak Bhardwaj25 Oct 20262 min read

Applies toSQL Server 2016 and later on-premises, Azure VMs and EC2; backup to disk, SMB shares, Azure Blob (URL) and S3-compatible storage (SQL Server 2022)

Measure current throughput

SELECT TOP 20 bs.database_name, bs.type, bs.backup_start_date,
       DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS secs,
       bs.backup_size / 1048576.0 AS size_mb,
       bs.compressed_backup_size / 1048576.0 AS compressed_mb,
       (bs.backup_size / 1048576.0) / NULLIF(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date), 0) AS mb_per_sec
FROM msdb.dbo.backupset bs
ORDER BY bs.backup_start_date DESC;

Levers

Lever	Effect
`COMPRESSION`	Less I/O and network; CPU cost. Usually faster overall
Multiple files (striping)	Parallel writes to targets
`BUFFERCOUNT`, `MAXTRANSFERSIZE`	Larger and more I/O buffers; test, uses memory
Faster target	Local NVMe staging, then copy; or more storage throughput
Backup to URL with block blobs	Stripe across several URLs for large databases
Differential backups	Smaller nightly backups between fulls

BACKUP DATABASE Sales
TO DISK = 'F:\Backup\Sales_1.bak', DISK = 'G:\Backup\Sales_2.bak',
   DISK = 'H:\Backup\Sales_3.bak', DISK = 'I:\Backup\Sales_4.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5,
     BUFFERCOUNT = 64, MAXTRANSFERSIZE = 4194304;

-- Backup to URL, striped, with managed identity/SAS credential set up
BACKUP DATABASE Sales
TO URL = 'https://stbackups.blob.core.windows.net/sql/Sales_1.bak',
   URL = 'https://stbackups.blob.core.windows.net/sql/Sales_2.bak'
WITH COMPRESSION, CHECKSUM, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536;

Test values in a non-production copy; too many buffers can cause memory pressure.

Faster restores

Lever	Effect
Instant file initialization (Perform Volume Maintenance Tasks for the service account)	Data files don't need zeroing
Pre-size log files appropriately	Log files are always zeroed; huge logs slow restore
Same striping and buffer settings	Parallel reads
Restore to fast storage	Throughput bound by target

Check IFI: SELECT instant_file_initialization_enabled FROM sys.dm_server_services;

Encryption and integrity

CHECKSUM on every backup; RESTORE VERIFYONLY doesn't replace real test restores.
Backup encryption adds CPU; measure with it on.

Test restore time

Schedule a monthly restore of a large database to a test server; record duration. Compare with the RTO (see the backup strategy guide).

Tip
Compression and striping alone often halve backup times. Try them before buying faster storage.

Checklist

Throughput baseline from msdb
Compression and checksum on
Striping and buffer settings tested
IFI enabled; log sizes reasonable
Restore duration tested against RTO