SQL database groth by backupset sizes

What we want?

To get the databases groth.

We don't have a DMV that save the databases size history, so we can't know it exactly.

We can create a process that get in each day/week/... the current size and save it to a table that we'll create, but if we don't have such a process and we want to know how our databases are encreased, it won't help us.

So, what we can do?

Get the database groth by backupset sizes, it's not exact numbers, but it can help us to analize the databases groth.

; with DbSizeLog as

(

       SELECT

              [database_name] as DatabaseName,

              DATEPART(month,[backup_start_date]) as BackupMonth,

              DATEPART(year,[backup_start_date]) as BackupYear,

              AVG([backup_size]/1024/1024) as BackupSizeMB,

              LAG (AVG([backup_size]/1024/1024), 1, 0)

                     OVER (PARTITION BY [database_name] ORDER BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(month,[backup_start_date]))

                     AS PrevBackupSizeMB

       FROM msdb.dbo.backupset

       WHERE  [type] = 'D'

       --AND         [database_name] = 'UniformStructure_Employers'

       GROUP BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(mm,[backup_start_date])

       --ORDER BY [database_name], DATEPART(year,[backup_start_date]), DATEPART(mm,[backup_start_date])

)

SELECT DatabaseName, BackupYear, BackupMonth,

              cast(BackupSizeMB as decimal(20,2)) as BackupSizeMB,

              cast(PrevBackupSizeMB as decimal(20,2)) as PrevBackupSizeMB,

              cast(((case PrevBackupSizeMB when 0.0 then NULL else (BackupSizeMB - PrevBackupSizeMB) end)) as decimal(20,2)) as GrothMB,

              cast((((case PrevBackupSizeMB when 0.0 then NULL else (BackupSizeMB/PrevBackupSizeMB) end) -1) *100) as decimal(5,2)) as GrothPrecentage

FROM   DbSizeLog

--WHERE BackupYear = 2017

--AND         DatabaseName IN ('DatabaseName', 'AnotherDatabaseName')

ORDER BY DatabaseName, BackupYear, BackupMonth