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:
*/
 
 DECLARE @t TABLE
(dbname VARCHAR(500), physical_device_name VARCHAR(2000)
, sizeMB NUMERIC(20,0), StartDate DATETIME
, FinishDate DATETIME,age_in_Days INT)
 
INSERT INTO @t
EXEC sp_MSforEachDb '
SELECT
TOP 1 (s.database_name)
,m.physical_device_name
,s.backup_size/1000000
,s.backup_start_date
,s.backup_finish_date
,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
WHERE
s.database_name = ''?''
and s.type =''D''
ORDER BY s.backup_finish_date desc
'
SELECT
SERVERPROPERTY('servername') 'ServerName',
SUM(sizeMB) 'TotalBackupMB',
MIN(StartDate),
sum(datediff(mi,(StartDate),(FinishDate))) 'DurationMinutes' -- This adds individaul backup times
FROM @t
 
SELECT dbname, physical_device_name, sizeMB,
Age_in_Days, StartDate, FinishDate,datediff(mi,(StartDate),(FinishDate)) 'DurationMinutes'
 
FROM @t