Thursday 23 August 2012

Get Database Backup Size Information

SELECT [database_name]
        ,[recovery_model]
        ,[backup_start_date]
        ,[backup_finish_date]
        ,CASE [type]
            WHEN 'D' THEN 'Full Backup'
            WHEN 'I' THEN 'Differential DB Backup'
            WHEN 'L' THEN 'Log Backup'
            WHEN 'F' THEN 'File or Filegroup Backup'
            WHEN 'G' THEN 'Differential File Backup'
            WHEN 'P' THEN 'Partial Backup'
            WHEN 'Q' THEN 'Differential Partial Backup'
        END AS [Backup Type]
        ,[backup_size]
        ,[compressed_backup_size]
 FROM [msdb].[dbo].[backupset]
ORDER BY [database_name], [backup_start_date]