Successful backups still deserve a duration trend. Backup throughput and duration trends make that change visible before the backup window becomes a problem. The history needs comparable backup types and careful device joins.
Start With Comparable Backup Sets
The backupset table records completed backup metadata in msdb. Filter the backup type before building a trend. Full database, differential, and log backups represent different amounts and kinds of work. Combining them in one average can make normal scheduling differences look like performance changes.
I begin with full database backups for one consistent report. Keep copy-only status available because an ad hoc copy can use a different destination or compression choice. The history dates describe the source server's recorded time. They are not automatically a portable UTC timeline across several instances.
The following query captures the last thirty days of full backups. DATEDIFF_BIG protects the duration calculation from narrow arithmetic assumptions. The duration is measured in whole seconds, so a subsecond operation can have a zero value. Retain that case without dividing by zero.
SELECT backup_set_id,database_name,backup_start_date,backup_finish_date,
backup_size,compressed_backup_size,media_set_id,is_copy_only,
DATEDIFF_BIG(second,backup_start_date,backup_finish_date) AS DurationSeconds
INTO #BackupThroughput
FROM msdb.dbo.backupset
WHERE type='D' AND backup_finish_date IS NOT NULL
AND backup_start_date>=DATEADD(day,-30,GETDATE());
SELECT database_name,backup_start_date,DurationSeconds,
backup_size/1048576.0 AS LogicalMiB,
compressed_backup_size/1048576.0 AS StoredMiB,
backup_size/1048576.0/NULLIF(DurationSeconds,0) AS LogicalMiBPerSecond,
compressed_backup_size/1048576.0/NULLIF(DurationSeconds,0) AS StoredMiBPerSecond
FROM #BackupThroughput ORDER BY database_name,backup_start_date;Distinguish Logical and Stored Backup Throughput
Backup_size and compressed_backup_size describe different byte quantities. The first provides the uncompressed backup-set size context. The second provides the stored backup byte count. Dividing each by duration produces two different rates that need different labels.
Do not compare logical throughput from one report with stored throughput from another and call the difference a speed change. Compression can reduce stored bytes while consuming CPU. Data compressibility can also change as the database content changes. Preserve both size columns to explain those effects.
These rates are operation-wide averages, not instantaneous disk measurements. They include the elapsed backup operation and cannot identify every bottleneck. A high logical rate does not mean the target device physically wrote that many uncompressed bytes per second. The distinction matters when compression ratios vary.
Trend Backup Throughput per Database With Window Functions
Compare the current duration with the preceding full backup and a short rolling average. Partition by database name and order by start time plus backup_set_id. The identifier breaks timestamp ties. A row-based average describes recent operations, not an exact fixed number of calendar days.
SELECT database_name,backup_start_date,DurationSeconds,
LAG(DurationSeconds) OVER
(PARTITION BY database_name ORDER BY backup_start_date,backup_set_id)
AS PreviousDurationSeconds,
AVG(CONVERT(decimal(20,2),DurationSeconds)) OVER
(PARTITION BY database_name ORDER BY backup_start_date,backup_set_id
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RecentAverageSeconds,
backup_size/1048576.0/NULLIF(DurationSeconds,0) AS LogicalMiBPerSecond
FROM #BackupThroughput
ORDER BY database_name,backup_start_date,backup_set_id;A duration increase can simply reflect more data. Compare size and rate beside duration before escalating. A stable rate with a larger backup points toward workload growth. A falling rate under comparable size and settings deserves a different investigation.
Join Device Details Without Inflating the Totals
A striped backup can have several backupmediafamily rows. Joining them directly to backupset produces several report rows for the same backup. If those rows feed SUM or AVG, the backup's duration and size receive extra weight. Aggregate device details before joining them to backup-level metrics.
WITH Devices AS
(
SELECT media_set_id,COUNT(*) AS DeviceRows,
STRING_AGG(CONVERT(nvarchar(max),physical_device_name),N'; ')
AS DeviceNames
FROM msdb.dbo.backupmediafamily GROUP BY media_set_id
)
SELECT b.database_name,b.backup_start_date,b.DurationSeconds,
b.backup_size,b.compressed_backup_size,d.DeviceRows,d.DeviceNames
FROM #BackupThroughput b
LEFT JOIN Devices d ON d.media_set_id=b.media_set_id
ORDER BY b.database_name,b.backup_start_date;STRING_AGG requires SQL Server 2017 or later. The displayed device row count is an inventory count, including any mirroring context present in that media set. It is not always the number of independent stripes used for one simple destination. Keep family and mirror details when investigating a complex media layout.
Investigate Backup Throughput at the Destination
Backup performance depends on source reads, compression CPU, and destination writes. A slow target disk or network path can extend the backup while the database engine's query workload remains normal. The history identifies the operation and destination; it does not directly measure the network or storage latency.
I compare the destination and compression settings before blaming a SQL configuration. A changed backup path can explain a changed rate. Which resource was saturated during the operation, and what else was using it? Collect targeted storage, CPU, and network evidence for that interval.
Also check concurrent backups, consistency checks, and other large reads. A quiet test cannot predict the rate during a crowded maintenance window. Keep the scheduling context beside the trend so a workload collision does not look like unexplained deterioration.
Treat Purged History as Missing Coverage
Administrative cleanup can remove old msdb history. A report beginning thirty days ago cannot guarantee thirty days of retained records. Check the earliest available set and known cleanup policy. Do not fill missing days with zero-duration backups.
SELECT database_name,MIN(backup_start_date) AS FirstRetainedBackup,
MAX(backup_start_date) AS LatestRetainedBackup,
COUNT_BIG(*) AS RetainedFullBackups
FROM msdb.dbo.backupset WHERE type='D'
GROUP BY database_name ORDER BY database_name;This inventory also lacks failed operations that never produced the relevant completed backupset record. Review job history and operational alerts for failures. Successful-set trends and failure monitoring answer different questions and belong together in the operational view.
Separate Speed From Recoverability
A quick backup is not proof that the recovery process works. Keep restore verification and restore testing under the established backup process. The throughput report helps manage the backup window; it does not validate the recovery point or recovery time by itself.
Likewise, a smaller stored size can reflect better compression rather than missing data. Read the uncompressed size and database context before deciding the backup is suspicious. A successful history row is a useful receipt, but the restore plan still needs an actual recovery test.
Preserve a Meaningful Baseline
Save captured metrics with server identity, capture time, backup type, and the selected date range. Compare the same definitions in later reports. If destinations, compression algorithms, or schedules change, mark those boundaries in the trend.
Use a representative series rather than reacting to one unusually slow operation. Then investigate the concrete resource responsible for a sustained change. That approach turns msdb history into an early warning supported by evidence, instead of a leaderboard of backup speeds without operational context.
Keep database identity changes in mind when grouping long histories. A reused database name or a restored copy can represent a different operational context. Preserve backup identifiers and source server identity in any exported report. When a database is renamed, annotate the timeline rather than assuming the two names describe unrelated workloads or that one name always identifies the same unchanged database.
Backup throughput comparisons need identical byte definitions and comparable backup types. Mark destination changes in the trend before interpreting a shift in the rate.
Related reading on this blog: Reclaiming Space and Performance: Database Backup History and Compressed Backup and Performance: SQL in Sixty Seconds #196.
Backup throughput is not a restore guarantee, it is an operation-wide trend that needs size, destination, and workload context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
