Get Backup File Size History

USE msdb

GO

--retrieves stats about backup data and file sizes

-- per backup

SELECT

logical_name AS BackupFile_LogicalName,

bs.backup_start_date AS Backup_StartDate,

bs.backup_finish_date AS Backup_EndDate,

bf.file_size/1024/1024 AS 'BackupFile_FileSize(MB)',

bf.backup_size/1024/1024 AS 'BackupFile_BackupSize(MB)'

FROM backupfile bf

INNER JOIN backupset bs

ON bf.backup_set_id = bs.backup_set_id

ORDER BY bs.backup_start_date DESC

-- log file size per day

-- (good for forecasting log shipping netowrk bandwidth consumption)

SELECT

logical_name AS BackupFile_LogicalName,

CONVERT(DATETIME, CONVERT(VARCHAR(11), bs.backup_finish_date, 113)) AS BackupDate,

AVG(bf.file_size/1024/1024) AS 'BackupFile_DailyFileSizeAverage(MB)',

SUM(bf.backup_size/1024/1024) AS 'BackupFile_DailyFileSizeTotal(MB)'

FROM backupfile bf

INNER JOIN backupset bs

ON bf.backup_set_id = bs.backup_set_id

GROUP BY logical_name, CONVERT(DATETIME, CONVERT(VARCHAR(11), bs.backup_finish_date, 113))

ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR(11), bs.backup_finish_date, 113)) DESC, logical_name