There are frequent cases when you have to create a graph with Backup size growth over time. For example, to estimate their growth in order to plan buying more space. If you don’t have any collection scripts put in place to gather sizes of databases or backups it is very simple to query msdb system database.
In msdb is stored each backup command along with database size, backup size, compressed backup size, file location, and many other usefull information.
The only case you cannot rely completely on msdb information is when you migrated user databases from a different server and chose to do it to a newly installed SQL server with new system databases. In this case, you will not find any information about backups, older than migration date in msdb. You will have to recover the information from older msdb backups if you archived them of course.
select convert (date,backup_start_date) Backup_Date, sum(compressed_backup_size/1073741824) as Sum_Compressed_Backup_Size_GB, sum(backup_size/1073741824) as Sum_Backup_Size_GB,COUNT(database_name) Total_DB_count from msdb.dbo.backupset where [type] in ('D','I','L') and backup_start_date > GETDATE ()-365 group by convert (date,backup_start_date) order by convert (date,backup_start_date)
 
declare @dw_sunday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =1) declare @dw_monday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =2) declare @dw_tuesday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =3) declare @dw_wednesday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =4) declare @dw_thursday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =5) declare @dw_friday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =6) declare @dw_saturday date = (select max(CONVERT (date, backup_start_date)) from msdb..backupset where DATEPART (dw,backup_start_date) =7) ;with a as (SELECT CONVERT (date,backup_start_date) as data, database_name, sum(compressed_backup_size/1048576) as [Backup_total(MB) Full] FROM msdb..backupset where [type] in ('D') and convert (date,backup_start_date) = @dw_saturday group by database_name, CONVERT (date,backup_start_date) ) , b as (SELECT convert (date,backup_start_date) as data, b.database_name, sum(compressed_backup_size/1048576) as [Backup_total(MB) Diff] FROM msdb..backupset b inner join a on a.database_name = b.database_name and convert (date,backup_start_date) > CONVERT(date,a.data) where [type] in ('I') group by b.database_name, CONVERT (date,backup_start_date)) select a.database_name, a.data as full_data, b.data as diff_data, a.[Backup_total(MB) Full], b.[Backup_total(MB) Diff] from a, b where a.database_name = b.database_name and b.data > a.data order by 1,2,3
SELECT distinct datepart(wk,backup_start_date) AS weekNumber, sum(compressed_backup_size/1073741824) as [Backup_total(GB)], COUNT(database_name) as Files_Total FROM msdb.dbo.backupset where backup_start_date > GETDATE ()-365 and backup_start_date < '2014-01-01 00:00:00.000' group by datepart(wk,backup_start_date) order by datepart(wk,backup_start_date)
order by date descending, including information about database file names and their sizes. This will allow you to estimate the growth of Database files, not only their backups.
select distinct b2.database_name, b2.backup_start_date, b2.type, b2.backup_size/1048576 as backupsize_GB, m2.physical_device_name, b.file_size/1048576 as File_size_MB, b.logical_name as Logical_FileName from msdb.dbo.backupfile b, msdb.dbo.backupset b2, msdb.dbo.backupmediafamily m2 where b.backup_set_id=b2.backup_set_id and b2.media_set_id = m2.media_set_id and --b2.database_name = 'master' AND b2.type = 'D' order by 2 desc