| SELECT
cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION,
SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,
b.backup_start_date,b.backup_finish_date,
Round(b.backup_size/(1024*1024),2) as Size_n_MB,
Physical_device_name
FROM msdb.dbo.backupset b,
( select a.DATABASE_NAME, MAX(a.backup_set_id) id
from msdb.dbo.backupset a
WHERE a.TYPE = 'D' and
a.DATABASE_NAME = DATABASE_NAME
group by a.DATABASE_NAME ) cc,
msdb.dbo.backupmediafamily f
WHERE TYPE = 'D'
AND b.backup_set_id = cc.id
AND b.media_set_id = f.media_set_id |
