Backup – Total Size Full Backups

This script is useful to find the size all last backups and an estimate on how long they took the last time they run.

It displays two queries. The first one gets the start time of the first database and end time of the last one. Naturally, if all databases were not backup at the same time this value will be off.

The second query is more reliable as it presents information for each database

It queries the MSDB for last backup on each database.


DESCRIPTION: Queries the MSDB database for the size of the last full backup of each database
Configuration: No Configuration needed
Two queires: individual sizes, and Total sizes PLUS DURATION
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
Does not work: MSSQL2000
 Update Log:
(dbname VARCHAR(500), physical_device_name VARCHAR(2000)
, sizeMB NUMERIC(20,0), StartDate DATETIME
, FinishDate DATETIME,age_in_Days INT)
EXEC sp_MSforEachDb '
TOP 1 (s.database_name)
,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days]
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
s.database_name = ''?''
and s.type =''D''
ORDER BY s.backup_finish_date desc
SERVERPROPERTY('servername') 'ServerName',
SUM(sizeMB) 'TotalBackupMB',
sum(datediff(mi,(StartDate),(FinishDate))) 'DurationMinutes' -- This adds individaul backup times
SELECT dbname, physical_device_name, sizeMB,
Age_in_Days, StartDate, FinishDate,datediff(mi,(StartDate),(FinishDate)) 'DurationMinutes'

Leave a Reply

Your email address will not be published. Required fields are marked *