Generate Backup list

Question

I have the query below that returns all databases and full backup history with the last backup date and device name. I am trying to modify the query to only return all the databases and just the last backup date and file location.

So now I get:
DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/25/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseA - 02/24/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/24/2014 - \\FileLocation
DatabaseA - 02/23/2014 - \\FileLocation
etc,etc
I would like to get:
 (Most Recent Full - Last File Location)
DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseD - 02/25/2014 - \\FileLocation
SELECT bs.database_name AS DatabaseName, bs.backup_finish_date, MAX(bms.physical_device_name) AS FullBackupName 
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id 
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name 
WHERE bs.type IN ('D')
AND s.name NOT IN ('master', 'msdb', 'model') 
GROUP BY bs.database_name , bs.backup_finish_date
ORDER BY bs.backup_finish_date DESC

Answer 1

It's not a modified version of your query, but I wrote this a while back (backupset is one of my favorite tables) that serves your purpose.

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