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

Comments

Robert said…
Very useful code, thanks for sharing!

Popular Posts