Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - 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
              


ċ
Get Backup File Size History.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:53 AM
Comments