This is my first post.
I'm having issues with a query below which works great and returns the db name along with when the backup was last completed - it works great however i'd like to add another join to the msdb.dbo.backupmediafamily table to include the file location.
Would it be possible to join this table along with the existing join?
SELECT db.name AS DatabaseName,
case when MAX(b.backup_finish_date) is NULL then 'No Backup Completed' else convert(varchar(100),
MAX(b.backup_finish_date)) end AS LastBackupFinishedDateTime
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
WHERE db.database_id NOT IN (2) AND db.database_id NOT IN ('1', '2', '3', '4')
GROUP BY db.name
ORDER BY 2 DESC
Thanks for taking the time to read.