Getting the Size of the Full Backups in an Instance

If you are planning for a restore of all the database in an instance to another instance, you might be interested in knowing the size of all the backups that you might have to restore.

The following query will help you get the size of all the full backups (if available) in the instance.

select Database_Name, 

       backup_start_date BackupStartDate , 

       convert(numeric(10,2),(compressed_backup_size/1000000)) 'BackupSizeIn MB',

       bmf.physical_device_name

from   msdb..backupset bs inner join 

       msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id

where  type = 'D'

and    backup_start_date = (select  max(backup_start_date) 

                            from    msdb..backupset bs1 

                            where   bs1.type = 'D' 

                            and     bs1.database_name = bs.database_name)

order by convert(numeric(10,2),(compressed_backup_size/1000000)) desc