A large database backup can finish long after the storage has gone quiet. Faster backups come from measuring the pipeline, then changing one transfer setting at a time. Guessing at the biggest buffer count is a quick way to trade a slow backup for an unhappy server.
Faster Backups Start With a Repeatable Baseline
Start with a full backup that represents the real database and destination. Use the same compression choice, checksum option, workload window, and storage path for every run. A backup to NUL shows how fast SQL Server can read and process data without writing a backup file. It is not a restorable backup, so it cannot stand in for the final test. Record both elapsed time and bytes written from msdb. The stopwatch alone hides compression differences.
I begin by asking whether the source reads, the backup destination, or CPU is busy. If storage reads are already saturated, adding output files will not change that bottleneck. If the destination is slow, a larger transfer can make the write pattern more efficient, but it cannot create bandwidth. What part of your backup is actually waiting?
See the Buffers Chosen by the Engine
SQL Server chooses defaults based on the database and devices. Trace flags 3213 and 3605 can print backup buffer configuration into the error log. They are diagnostic flags, and Microsoft's support material warns against leaving them enabled casually. Use a controlled test, capture the relevant lines, then turn the flags off. The example uses a session scope rather than the global -1 scope.
DBCC TRACEON (3213, 3605);
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQLBackups\YourDatabase_baseline.bak'
WITH COPY_ONLY, INIT, CHECKSUM, COMPRESSION, STATS = 10;
DBCC TRACEOFF (3213, 3605);Change the database and path first. Confirm the SQL Server service account can write there. COPY_ONLY avoids disturbing a differential base. INIT overwrites the named file, so use a dedicated test name and keep it away from your production backup chain. If the backup errors, switch the flags off in the same session before closing it. Read the error log for the buffer count and transfer size that this specific run used.
Test BUFFERCOUNT by Itself for Faster Backups
BUFFERCOUNT controls the number of I/O buffers available to the backup operation. More buffers can keep the pipeline busy when it was starved, but each buffer consumes memory. Approximate buffer memory as BUFFERCOUNT multiplied by MAXTRANSFERSIZE. Doubling one while holding the other fixed doubles that part of the memory demand. Do not put a large number into every backup job because one test ran well on an idle server.
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQLBackups\YourDatabase_buffers.bak'
WITH COPY_ONLY, INIT, CHECKSUM, COMPRESSION,
BUFFERCOUNT = 64, STATS = 10;The value 64 is a test case, not a recommendation. Compare it with the baseline and a smaller value in the same maintenance conditions. Record elapsed seconds, backup size, throughput, and memory pressure during the run. If throughput barely moves while memory demand rises, the bottleneck is elsewhere. A test that makes application queries slower has failed, even if the backup ends earlier.
Test MAXTRANSFERSIZE Separately
MAXTRANSFERSIZE sets the largest transfer unit between SQL Server and backup media. For disk backups, valid values are multiples of 64 KB up to 4 MB. Keep BUFFERCOUNT at its baseline while testing a transfer size, then calculate memory again. Compression and encryption can affect the defaults, so the actual baseline matters more than a number copied from another instance.
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQLBackups\YourDatabase_transfer.bak'
WITH COPY_ONLY, INIT, CHECKSUM, COMPRESSION,
MAXTRANSFERSIZE = 1048576, STATS = 10;One megabyte is a readable starting test, not an automatic winner. Watch the SQL Server process and other memory consumers while the backup runs. If an attempt fails for lack of memory, back out the change before experimenting again. I would rather keep a stable backup that finishes in its window than publish a record time that only works on an empty server.
Add Backup Stripes Only After the Single-File Tests
Multiple backup files let the operation write across devices or paths. They help most when one destination path limits throughput and the additional paths lead to real independent capacity. Four filenames on one saturated volume do not create four disks. Change only the number of stripes for this test. Keep the other settings at their baseline values, and restore from the full set of files as one backup media set.
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQLBackups\YourDatabase_1.bak',
DISK = N'E:\SQLBackups\YourDatabase_2.bak'
WITH COPY_ONLY, INIT, CHECKSUM, COMPRESSION, STATS = 10;Backup stripes complicate file handling. Losing one stripe makes the set unusable. Confirm that both paths are retained, copied, and protected together. A new layout also changes the restore command and the runbook. A faster backup is not an improvement if the restore team only knows about the first file.
Record Throughput and Memory for Every Run
msdb.dbo.backupset stores the start and finish times and compressed backup size. The query below lists recent full database backups for the test database. Divide bytes by elapsed seconds for a practical throughput comparison. Record buffer memory separately from the settings used, because msdb does not store every transfer option as a tidy experiment record.
SELECT TOP (20) backup_start_date, backup_finish_date,
backup_size, compressed_backup_size,
DATEDIFF(second, backup_start_date, backup_finish_date)
AS elapsed_seconds
FROM msdb.dbo.backupset
WHERE database_name = N'YourDatabase' AND type = 'D'
ORDER BY backup_start_date DESC;Keep a short table with test name, destination, stripe count, BUFFERCOUNT, MAXTRANSFERSIZE, elapsed time, compressed bytes, calculated throughput, and peak memory observation. Repeat a promising run to rule out a quiet interval or warm cache. A single lucky backup has excellent public relations and limited engineering value.
Prove That Faster Backups Still Restore
Run RESTORE VERIFYONLY against each complete backup set as a quick media check, then perform a real restore to an isolated test instance. VERIFYONLY does not prove that the database will recover and serve queries. Check that the restore procedure knows the file list, encryption prerequisites, and target paths. Keep the selected settings only if they improve backup time without hurting the workload and the restored copy works.
I also revisit the result after storage, compression, or database size changes. The best values belong to a particular pipeline, not to SQL Server in general. Leave the measurements with the backup job so the next person can explain the numbers. When the pipeline changes, run the experiment again instead of trusting an old winner.
Related reading on this blog: How to Know Backup Speed in SQL Server? Interview Question of the Week #277 and Creating Multiple Backup Files: Stripped.
A faster backup is not a win by itself, it is useful when memory cost and restore are understood.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
