--Let's look at the whole table!
select * from msdb.dbo.backupset

--Let's take a backup and then look again!
backup database test
to disk='C:\DBFiles\test_compressed.bak'
with init,compression

--When was the last full?
select
	database_name
	,max(backup_finish_date) [last_backup]
from
	msdb.dbo.backupset
where type='D'
group by
	database_name

--How long are the fulls
select
	database_name
	,backup_finish_date
	,datediff(ss,backup_start_date,backup_finish_date) backup_time_seconds
from
	msdb.dbo.backupset
where type = 'D'
order by backup_finish_date desc

--When was the last full and where is it?
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'



