Query to show last date-time and type backups?

Question

Hello, I currently have the following to get a Linked Server's last 'Full' backup date:

SELECT
'My_Linked_Server',
database_name,
MAX(backup_finish_date) AS last_db_backup_date
FROM [My_Linked_Server].msdb.dbo.backupmediafamily
INNER JOIN [My_Linked_Server].msdb.dbo.backupset ON backupmediafamily.media_set_id = backupset.media_set_id
inner join [My_Linked_Server].master.sys.databases ON databases.name = backupset.database_name
WHERE backupset.type = 'D' and state_desc = 'ONLINE'
GROUP BY
backupset.database_name
ORDER BY
backupset.database_name

I now want to remove the backupset.type = 'D' ( which references it was a Full backup) in the Where clause and return any type with a new column named 'BackupType' as Microsoft lists:

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

Can be NULL.

And instead of showing the one character from list above I'd like it to show the actual real Backup type spelled out that it relates to.

What would be the query that would do this (perhaps just slightly tweaking my current query) ?

Thanks in advance.

Answer 1

Maybe this query will work:

SELECT 'My_Linked_Server', database_name, 
   MAX(backup_finish_date) AS last_db_backup_date,
   case backupset.type
      when 'D' then 'Database'
      when 'I' then 'Differential database'
      when 'L' then 'Log'
      when 'F' then 'File or filegroup'
      when 'G' then 'Differential file'
      when 'P' then 'Partial'
      when 'Q' then 'Differential partial' end as [Type]
FROM [My_Linked_Server].msdb.dbo.backupmediafamily
INNER JOIN [My_Linked_Server].msdb.dbo.backupset ON backupmediafamily.media_set_id = backupset.media_set_id
inner join [My_Linked_Server].master.sys.databases ON databases.name = backupset.database_name
WHERE state_desc = 'ONLINE'
GROUP BY backupset.database_name, backupset.type
ORDER BY backupset.database_name, backupset.type

You can also put the types and abbreviations to some helper table and use a JOIN.

Answer 2

Excellent, that was exactly what I was looking for !

Thanks Viorel-1 for your perfect & quick solution.