Calculating total backup size of all database on a SQL Server

Sometime we need to calculate what is the total size of all backups of all databases hosted on a SQL Server instance. In that case, please use below code.

Note: This code will calculate total size of full backups of all databases hosted on the SQL Server instance in GB and MB. However you may change the combination of device_type and type value to suit your specific query.

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

Please Note: This code is tested on SQL 2005/2008/2008 R2