When the daily backup history is too granular for a high-level capacity planning overview, a monthly view gives a cleaner picture of long-term growth trends. This query aggregates backup data from msdb..backupset by month, taking the peak size reached in each month to calculate the net monthly change.

This complements the daily report: use the daily view for short-term anomaly detection, and the monthly view for presenting growth forecasts to stakeholders or sizing future storage.

Monthly Size Change Report

The query groups backup entries by year-month (extracted with CONVERT and LEFT), takes the MAX backup size per month, then pairs each month with the previous one to compute the delta. Configure only the database name at the top.

DECLARE @dbname NVARCHAR(1024)

-- Configure HERE: database name
SET @dbname = 'YourDatabaseName'

;WITH TempTable (Row, database_name, backup_start_date, Mb) AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY LEFT(CONVERT(NVARCHAR(20), backup_start_date, 112), 6)) AS Row,
        database_name,
        LEFT(CONVERT(NVARCHAR(20), backup_start_date, 112), 6)  AS month,
        MAX(CAST(backup_size / 1024.0 / 1024.0 AS DECIMAL(10,2)))  AS Mb
    FROM msdb..backupset
    GROUP BY database_name,
             LEFT(CONVERT(NVARCHAR(20), backup_start_date, 112), 6),
             type
    HAVING type = 'D'
       AND database_name = @dbname
)
SELECT
    A.database_name,
    A.backup_start_date         AS month,
    A.Mb                        AS max_backup_size_mb,
    A.Mb - B.Mb                 AS delta_mb
FROM TempTable A
LEFT JOIN TempTable B ON A.Row = B.Row + 1
ORDER BY database_name, backup_start_date;

The delta_mb column shows the net growth compared to the previous month. A consistently growing delta is a sign of healthy data accumulation; sudden spikes may indicate uncontrolled data insertion, bulk loads, or index maintenance operations that were not expected. This data feeds naturally into storage forecasting models.