How to find physical_device_name on backup by extending great query from Ozar? Found this on an older blog post at BrentOzar.com:

SELECT  d.name, MAX(b.backup_finish_date) AS last_backup_finish_date
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
ORDER BY 2  


Pretty great query!

Question: is there is an elegant way to join on msdb.dbo.backupmediafamily and return the physical_device_name for those databases that DO have recent backups? Result set should still return the ones without backups. 

My attempts thus far stink :-)

Link to blog post:
https://www.brentozar.com/archive/2012/03/how-talk-your-boss-about-world-backup-day/

Thanks!
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