Part of the DBA-Tools Project.
What Your Backup Sizes Are Quietly Telling You
Nobody watches backup file sizes month to month, they just watch that the backup succeeded. But backup size is a free, already-collected proxy for data growth rate, and it catches two things a success/failure check never will: a database whose backups are unexpectedly shrinking (data loss, a truncation that shouldn’t have happened, a job that silently started backing up the wrong thing), and a database growing fast enough that storage and backup-window planning need to happen now, not next quarter.
This script pulls twelve months of backup history from msdb and rolls it up to one row per database, per backup type, per month, so the trend is visible at a glance instead of buried in hundreds of individual backup rows.
Why Backup Size Trend Matters
- A month where a database’s full backup size drops sharply is worth investigating immediately, it can mean real, unexpected data loss, not just less activity
- Steady month-over-month growth tells you when a database will outgrow its current backup window or storage allocation, well before it becomes an emergency
avg_compression_pctmoving over time can flag a change in the data itself (more already-compressed data like images, or a schema change) as easily as a backup configuration change- This is a trend script, not a point-in-time check, one month’s number means little, the shape of the line across twelve months is the actual signal
When to Run This Script
- Monthly, as part of a capacity-planning or storage-review cadence, not just when something already looks wrong
- After any backup job or maintenance plan change, to confirm sizes are still behaving as expected
- When investigating a sudden change in backup duration or storage consumption, this shows whether size growth explains it
- Alongside Database Sizes and Free Space when building a full storage capacity picture
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-BackupSizeTrend
Category : backups
Purpose : Monthly backup size trend per database over the last 12 months, an indirect
proxy for data growth rate. Shrinking backups can indicate unexpected data
loss; growing backups inform storage planning.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-backup-size-trend/)
Requires : msdb access (db_datareader on msdb or sysadmin)
*/
-- Blog: https://sqldba.blog/dba-scripts-get-backup-size-trend/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
database_name,
CASE type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
ELSE type
END AS backup_type,
CAST(YEAR(backup_finish_date) AS CHAR(4)) + '-' +
RIGHT('0' + CAST(MONTH(backup_finish_date) AS VARCHAR(2)), 2) AS year_month,
COUNT(*) AS backup_count,
CAST(AVG(backup_size) / 1073741824.0 AS DECIMAL(10,3)) AS avg_size_gb,
CAST(MAX(backup_size) / 1073741824.0 AS DECIMAL(10,3)) AS max_size_gb,
CAST(MIN(backup_size) / 1073741824.0 AS DECIMAL(10,3)) AS min_size_gb,
CAST(AVG(CASE WHEN compressed_backup_size > 0
THEN compressed_backup_size END)
/ 1073741824.0 AS DECIMAL(10,3)) AS avg_compressed_gb,
CAST(AVG(CASE WHEN compressed_backup_size > 0 AND backup_size > 0
THEN CAST(compressed_backup_size AS FLOAT) / backup_size * 100
END) AS DECIMAL(5,1)) AS avg_compression_pct
FROM msdb.dbo.backupset
WHERE backup_finish_date >= DATEADD(MONTH, -12, GETDATE())
AND type IN ('D', 'I', 'L')
GROUP BY database_name, type, YEAR(backup_finish_date), MONTH(backup_finish_date)
ORDER BY database_name, backup_type, year_month;
One row per database, per backup type, per month, pulled straight from msdb.dbo.backupset, no other server required.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Get 12 months of backup size trend per database:
.\run.ps1 Get-BackupSizeTrend
# To run against a remote sql server:
.\run.ps1 Get-BackupSizeTrend -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Real output from this lab instance, not staged (71 rows total across the last 5 months this box has backup history for, condensed here):
| database_name | backup_type | year_month | backup_count | avg_size_gb | avg_compression_pct |
|---|---|---|---|---|---|
| BackupProgressDemo | Full | 2026-07 | 1 | 3.08 | 100.0 |
| GrowthLab | Full | 2026-02 | 1 | 0.80 | 1.0 |
| GrowthLab | Full | 2026-05 | 3 | 0.80 | 1.0 |
| GrowthLab | Full | 2026-07 | 1 | 0.87 | 1.0 |
| DemoDatabase | Full | 2026-05 | 3 | 0.01 | 9.0 |
| DemoDatabase | Full | 2026-07 | 1 | 0.01 | 7.9 |
GrowthLab’s own trend is a small, genuine example of exactly what this script is for: 0.80 GB in February, steady at 0.80 GB through May, up to 0.87 GB by July, consistent, gradual growth with nothing alarming in the shape of the line.
Understanding the Results
- avg_size_gb dropping between months — investigate before assuming it’s fine. Check for a truncation, an archival job, or a backup that silently started excluding data
- avg_size_gb climbing steadily — normal for an active database, feed it into capacity planning for storage and backup window duration, not an immediate concern on its own
- avg_compression_pct — a falling compression percentage over time can mean the data mix shifted toward already-compressed content (images, encrypted columns), which also affects CPU cost during backup
- backup_count — a month with fewer backups than expected is itself a finding, cross-check against Backup Coverage to confirm the schedule is actually running as intended
Best Practices
- Review this monthly as part of a standing capacity or storage cadence, not just reactively
- Treat any sharp drop in
avg_size_gbas an incident-worthy finding until proven otherwise, silent data loss is the worst-case explanation - Pair with Database Sizes and Free Space and Backup Restore Duration Estimate for a full picture of where storage and backup-window pressure is heading
- Twelve months is the window this script covers by design, long enough to see a real trend, short enough to stay fast; adjust the
DATEADD(MONTH, -12, ...)window if your retention or reporting cadence needs more history
Related Scripts
You may also find these scripts useful:
- Backups and Recovery (hub)
- Diff Backup Script
- Full Backup Script
- Restore Script
- T Log Backup Script
- Backup Chain Integrity
- Backup Coverage
- Backup Encryption Status
- Backup Restore Duration Estimate
- Database Backup History
Frequently Asked Questions
Why 12 months instead of all available history?
Twelve months is enough to see a genuine trend, including any seasonal pattern in data volume, without scanning years of msdb.dbo.backupset history on a busy instance. Extend the DATEADD window if you need a longer view for a specific investigation.
Does a shrinking backup always mean data loss?
Not always, an archival job, a deliberate cleanup, or a change to what’s included in the backup can all explain it legitimately. But it’s always worth confirming which of those it is rather than assuming, since real unexpected data loss looks exactly the same in this report.
Summary
Backup size is data SQL Server already tracks for you, msdb.dbo.backupset has the full history, this script just turns twelve months of it into one readable trend per database. A shrinking backup deserves the same urgency as a failed one, and a steadily growing one is exactly the early signal capacity planning needs.
Add this to a monthly review alongside the rest of the backup and storage series, and treat any unexplained drop in size as worth investigating immediately, not filed away for later.
Leave a Reply