Get Backup File Size History

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 10 Jan 2012

Description

Retrieves the file size history for backup files for all dataabses (Full, Differential and Transaction Log backups). Also performs some daily aggregation calculations to aid in space/bandwidth forecasting - especially useful when log shipping over a restricted bandwidht pipe

Code

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