How to find physical_device_name on backup by extending great query from Ozar?
WITH backups (DatabaseName, LastBackupFinishDate)
AS
(   
    SELECT d.name AS DatabaseName, MAX(b.backup_finish_date) AS LastBackupFinishDate 
    FROM master.sys.databases d WITH (NOLOCK)
    LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) 
        ON d.name = b.database_name AND b.type = 'D' 
    WHERE d.name &lt;&gt; 'tempdb' 
    GROUP BY d.name
)
SELECT  
    b.DatabaseName,
    b.LastBackupFinishDate,
    bmf.physical_device_name
FROM backups b 
LEFT JOIN msdb.dbo.backupset bs ON b.DatabaseName = bs.database_name
LEFT JOIN msdb.dbo.backupmediaset bms ON bs.media_set_id = bms.media_set_id
LEFT JOIN msdb.dbo.backupmediafamily bmf ON bms.media_set_id = bmf.media_set_id