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'