Latest Backup File Location

 SELECT DISTINCT a.database_name, a.max_backup_finish_date, d.compressed_backup_size_in_MBs, d.duration, d.[user_name], d.physical_device_name
FROM
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D' --'I for diffs and 'L' for logs
GROUP BY d.[database_name]) a
INNER JOIN
(
SELECT
 d.database_name
,d.backup_finish_date
, (compressed_backup_size/1024/1024) AS compressed_backup_size_in_MBs
, CASE
WHEN DATEDIFF (HOUR, d.backup_start_date, d.backup_finish_date) = 0
THEN CONVERT (VARCHAR (2), DATEDIFF (MINUTE, d.backup_start_date, d.backup_finish_date))+' Minutes'
END AS duration
, [user_name]
, physical_device_name
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D') d --'I for diffs and 'L' for logs
ON a.database_name = d.database_name
AND a.max_backup_finish_date = d.backup_finish_date