Trying to join from sys.databases and msdb.dbo.backupmediafamily

Transact-SQL
1

Hi Guys,

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.
Many thanks
Matt

2

can you try this

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,
f.physical_device_name
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
left outer join msdb.dbo.backupmediafamily f
on f.media_set_id=b.media_set_id
WHERE db.database_id NOT IN (2) AND db.database_id NOT IN ('1', '2', '3', '4')
GROUP BY db.name,f.physical_device_name
ORDER BY 2 DESC

1 Like
3

Hi Ahmed,

This is great.
Thank you very much for this, much appreciated!

Have a great day.
Matt

1 Like