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)

&nbsp

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)

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