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

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

1 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?
    ,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]
    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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.