Script to find backup size of all SQL Server databases backup size

USE msdb
GO

/* Please change device_type and backupset type combination as you need.
 
backupmediafamily device_type
 
2 = File
 
5 = Tape
 



backupset type
 
L = Log
 
I = Differential
 
D = Full
 
*/
SELECT (SUM(backup_size) + SUM(1536)) / 1024 / 1024 AS MBs
 ,(SUM(backup_size) + SUM(1536)) / 1024 / 1024 / 1024 AS GBs
FROM backupset
INNER JOIN (
 SELECT database_name
  ,MAX(backup_start_date) AS LastFullBackupDate
 FROM backupset
 WHERE media_set_id IN (
   SELECT media_set_id
   FROM backupmediafamily
   WHERE device_type = 2
   )
  AND type = 'D'
 GROUP BY database_name
 ) AS GetLastDate ON backupset.database_name = GetLastDate.database_name
 AND backupset.backup_start_date = GetLastDate.LastFullBackupDate