question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked techresearch7777777-7743 answered

Query to show last date-time and type backups?

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.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered

Excellent, that was exactly what I was looking for !

Thanks Viorel-1 for your perfect & quick solution.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.