Capacity planning starts with understanding how your databases grow over time. The simplest source of historical size data in SQL Server is the backup catalog: msdb..backupset records information about every backup taken on the instance. If you don’t have a dedicated monitoring tool, this is a reliable starting point.

Two important caveats before using this approach:

• msdb..backupset stores the backup size, not the datafile size. This reflects the actual data stored — not the allocated space inside the data files, which is typically larger.
• Full database backups include a small portion of the transaction log needed for recovery. The size reported is therefore a close but not exact representation of your data volume.

Daily Size Change Report

This query uses a CTE with ROW_NUMBER() to pair each backup with the previous one, calculating the daily increment in megabytes. Configure the database name and the number of days to analyze at the top.

DECLARE @dbname NVARCHAR(1024)
DECLARE @days   INT

-- Configure HERE: database name and number of days to analyze
SET @dbname = 'YourDBName'
SET @days   = 365

;WITH TempTable (Row, database_name, backup_start_date, Mb) AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY backup_start_date) AS Row,
        database_name,
        backup_start_date,
        CAST(backup_size / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS Mb
    FROM msdb..backupset
    WHERE type = 'D'
      AND database_name     = @dbname
      AND backup_start_date > GETDATE() - @days
)
SELECT
    A.database_name,
    A.backup_start_date,
    A.Mb                AS daily_backup_mb,
    A.Mb - B.Mb         AS increment_mb
FROM TempTable A
LEFT JOIN TempTable B ON A.Row = B.Row + 1
ORDER BY database_name, backup_start_date;

The increment_mb column shows the size difference compared to the previous backup. Negative values indicate data deletions or index rebuilds that reduced the actual data volume. For a monthly view of the same data, see the companion article on monthly size changes.