see when the last backup was taken on a SQL Server

[Costas]

 
 

--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'