DBA Scripts: Get Backup Size Trend

What Your Backup Sizes Are Quietly Telling You

The backup drive is filling faster than planned, or a full backup suddenly ran long, and you need to know which database grew and when. This rolls msdb backup history up to one row per database, per backup type, per month, so the change shows in a few lines instead of hundreds of 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_pct moving 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 a backup suddenly takes longer or uses more storage, to see whether database growth is the cause
• 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    : any login that can connect (msdb grants SELECT on dbo.backupset to public)
*/
-- 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 (MS Docs), 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:

• sql/backups/Get-BackupSizeTrend.sql
• powershell/wrappers/backups/Get-BackupSizeTrend.ps1

Example Output

One run on the lab instance returned 151 rows across 96 databases. These are the 10 rows for 4 of them, with max_size_gb and min_size_gb left out for width.

Read down each database. GrowthLab’s full backup grew from 0.804 GB to 0.875 GB over 2 months, 8.8%, steady growth to plan storage around. SalesDemo’s grew 60%, from 0.128 GB to 0.205 GB, yet its backup file stayed at 0.022 GB because the new data compressed well.

When a drive is filling, read avg_compressed_gb: it is what lands on disk. avg_size_gb is the data before compression, so on GrowthLab 0.875 GB of data wrote a 0.009 GB file.

Understanding the Results

Best Practices

• Review this monthly as part of a standing capacity or storage cadence, not just reactively
• Treat any sharp drop in avg_size_gb as 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

Frequently Asked Questions

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.

Related