SQL Server Backup History: Duration and Size Report

calendar Apr 13, 2026 / · 6 min read · sql server backup recovery msdb sql scripts database administration backup monitoring maintenance scripts reporting sys.databases backupset  ·
Share on: linkedin copy

Query Backup Duration and File Size from SQL Server History

This script queries SQL Server's msdb backup history to report the backup type, start and finish time, duration in minutes, and file size in both megabytes and gigabytes for every backup across all user databases on the server.

Purpose and Overview

Knowing that a backup completed is only part of the picture. DBAs also need to understand how long each backup took and how large the resulting files are. A backup that used to finish in ten minutes now taking an hour is worth investigating. A backup file that is half its usual size may indicate a failed or partial operation. This script, adapted from the DBA-Scripts collection by Bulent Gucuk, joins sys.databases with msdb.dbo.backupset and msdb.dbo.backupmediafamily to surface both duration and size in a single result set. Running it regularly gives you a baseline so that anomalies are easy to spot.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1USE master;
 2GO
 3SELECT
 4    @@SERVERNAME                                                        AS server_name,
 5    sdb.name                                                            AS database_name,
 6    sdb.recovery_model_desc,
 7    CASE bus.type
 8        WHEN 'D' THEN 'Full Backup'
 9        WHEN 'I' THEN 'Differential Backup'
10        WHEN 'L' THEN 'Transaction Log Backup'
11        ELSE bus.type
12    END                                                                 AS backup_type,
13    bus.backup_start_date,
14    bus.backup_finish_date,
15    DATEDIFF(MINUTE, bus.backup_start_date, bus.backup_finish_date)     AS duration_minutes,
16    CAST(bus.backup_size / 1024000.0 AS DECIMAL(19, 2))                AS size_mb,
17    CAST(bus.backup_size / 1024000000.0 AS DECIMAL(19, 2))             AS size_gb,
18    bmf.physical_device_name
19FROM sys.databases AS sdb
20LEFT OUTER JOIN msdb.dbo.backupset AS bus
21    ON bus.database_name = sdb.name
22LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bmf
23    ON bus.media_set_id = bmf.media_set_id
24WHERE sdb.name NOT IN ('tempdb')
25    AND sdb.database_id > 4
26    AND bmf.physical_device_name NOT LIKE '{%'
27ORDER BY sdb.name, bus.backup_finish_date DESC
28OPTION (RECOMPILE);

Base Table: sys.databases

The query starts with sys.databases as the driving table. Using a LEFT OUTER JOIN from here to msdb.dbo.backupset means that any user database with no backup history still appears in the result with NULL values in the backup columns. A NULL backup_start_date is an immediate flag that the database has never been backed up, at least not with history still present in msdb.

The filter sdb.database_id > 4 excludes the four system databases (master, model, msdb, tempdb) that are assigned IDs 1 through 4. tempdb is also excluded by name in the WHERE clause as a belt-and-suspenders measure, since tempdb is never a candidate for backup.

Joining msdb Backup History Tables

msdb.dbo.backupset contains one row per backup operation, storing the database name, backup type, start and finish dates, and the uncompressed backup size in bytes. The join uses bus.database_name = sdb.name, which matches on the name recorded at backup time.

msdb.dbo.backupmediafamily stores the physical location of the backup file — the full file path for disk backups or the tape device name. The join uses bus.media_set_id = bmf.media_set_id, linking a backup set to its storage destination.

The filter bmf.physical_device_name NOT LIKE '{%' removes virtual device interface entries that appear when some third-party backup tools register backup devices. These entries are not useful for reporting file paths.

Backup Type Decoding

The CASE expression translates the single-character bus.type codes into readable labels. SQL Server uses D for full database backups, I for differential backups, and L for transaction log backups. The ELSE bus.type clause passes through any other code — such as F (file and filegroup) — without hiding it.

Duration and Size Calculations

DATEDIFF(MINUTE, bus.backup_start_date, bus.backup_finish_date) computes the number of whole minutes elapsed between the start and finish of the backup operation. For backups that complete in under a minute, this returns zero; use DATEDIFF(SECOND, ...) if sub-minute precision matters.

The size columns divide bus.backup_size — stored in bytes — by 1,024,000 for megabytes and 1,000,000,000 for gigabytes, then cast the result to DECIMAL(19, 2) for two decimal places. Note that backup_size represents the uncompressed logical size of the data backed up, not the size of the file written to disk when backup compression is enabled. To see the compressed file size use bus.compressed_backup_size if available (SQL Server 2008 and later with Enterprise or Developer edition, or any edition from SQL Server 2016 SP1 onward).

Ordering

Results are ordered by sdb.name and then bus.backup_finish_date DESC so that for each database the most recent backup appears first. This layout is easy to scan when reviewing last-night's backup window across many databases.

Key Benefits and Use Cases

  • Identifies databases with no backup history (NULL finish date)
  • Shows backup duration trends to detect slow backups before they breach maintenance windows
  • Displays uncompressed size for capacity planning of backup storage
  • Covers all backup types (full, differential, transaction log) in one query
  • Includes the physical file path to confirm backups are landing in the correct location
  • Recovery model column helps explain why some databases have no log backup rows

Performance Considerations

  • msdb table growth: On busy servers, msdb.dbo.backupset can grow very large. Use msdb.dbo.sp_delete_backuphistory on a schedule to remove rows older than your retention window. Keeping 60 to 90 days of history is common.
  • Compressed vs. uncompressed size: backup_size reports the logical data size, not the on-disk file size. Enable backup compression and query compressed_backup_size alongside backup_size to understand your compression ratio.
  • High backup frequency environments: Sites with hundreds of databases and frequent log backups generate millions of rows in backupset over time. Add a WHERE bus.backup_finish_date >= DATEADD(DAY, -7, GETDATE()) filter to scope the result to a rolling window if the query is slow.
  • SQL Server 2022 and later: The sys.dm_database_backups dynamic management view surfaces recent backup metadata from the buffer cache without a full msdb scan. For near-real-time status checks it is faster, though it does not retain as much history as msdb.

Practical Tips

To limit the report to full backups only, add AND bus.type = 'D' to the WHERE clause. To include only backups completed in the last 30 days, add AND bus.backup_finish_date >= DATEADD(DAY, -30, GETDATE()).

To flag backups that took longer than a threshold — say 60 minutes — wrap the query in a CTE and filter on duration_minutes > 60:

1WITH backup_history AS (
2    -- paste the main SELECT here
3)
4SELECT *
5FROM backup_history
6WHERE duration_minutes > 60
7ORDER BY duration_minutes DESC;

Schedule this script as a SQL Server Agent job that writes results to a monitoring table. Compare each night's results against historical averages to detect gradual growth in backup time before it causes a missed maintenance window.

If you manage multiple instances, run the query from a central management server or use a linked server approach, adding @@SERVERNAME (already included) to the output so rows from different instances are distinguishable after being combined.

Conclusion

This backup history script gives DBAs a fast, repeatable way to check both duration and size for every backup on an instance. By starting from sys.databases and LEFT JOINing to the msdb history tables, it surfaces databases with missing backups alongside those with full history. The duration and size columns provide the baseline data needed to detect gradual performance degradation in backup jobs and to plan backup storage capacity. Pair it with a scheduled Agent job and a monitoring table to build a lightweight backup audit trail that requires no third-party tooling.

References

Posts in this series