1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--https://gist.github.com/anonymous/e925d700ad70b52ef57c
select
bs.database_name
,bs.backup_finish_date
,bs.backup_size/1024.0/1024.0 [backup_size_mb]
,bs.compressed_backup_size/1024.0/1024.0 [compressed_size_mb]
,datediff(ss,backup_start_date,backup_finish_date) [backup_time]
,((bs.backup_size/1024.0/1024.0)/(datediff(ss,backup_start_date,backup_finish_date)+1)) [mb_per_second]
,1.0-(bs.compressed_backup_size*1.0/bs.backup_size) [compression_ratio]
,bm.physical_device_name
from
msdb.dbo.backupset bs
join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
join (select database_name
,max(backup_finish_date) last_backup
from msdb.dbo.backupset
where type = 'D'
group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'
origin - http://www.pipiscrew.com/?p=3489 sql-see-when-the-last-backup-was-taken-on-a-sql-server