May 30, 2019 at 8:38 am
Hi All
I use the below to get the last FULL, DIFF & LOG backups for my SQL instances.
SELECT database_name ,
MAX(CASE type
WHEN 'D' THEN backup_finish_date
ELSE NULL
END) AS DatabaseBackup ,
MAX(CASE type
WHEN 'I' THEN backup_finish_date
ELSE NULL
END) AS DIFFBackup ,
MAX(CASE type
WHEN 'L' THEN backup_finish_date
ELSE NULL
END) AS LogBackup
FROM msdb.dbo.backupset
GROUP BY database_name
order by database_name
How would I modify this to add more info here from the msdb.dbo.backup set table, like backup_start_date, is_copy_only...etc etc.
Thanks
May 31, 2019 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 31, 2019 at 4:23 pm
SELECT database_name ,
backup_start_date,
is_copy_only,
MAX(CASE type
WHEN 'D' THEN backup_finish_date
ELSE NULL
END) AS DatabaseBackup ,
MAX(CASE type
WHEN 'I' THEN backup_finish_date
ELSE NULL
END) AS DIFFBackup ,
MAX(CASE type
WHEN 'L' THEN backup_finish_date
ELSE NULL
END) AS LogBackup
FROM msdb.dbo.backupset
GROUP BY database_name, backup_start_date,is_copy_only
order by database_name
May 31, 2019 at 8:27 pm
You would probably want to use OUTER APPLY instead of the CROSS TAB approach then:
SELECT LEFT(d.name,20) AS DBname, LEFT(d.state_desc,10) AS StateDesc, LEFT(d.recovery_model_desc,10) AS RecoveryModel,
fb.backup_start_date, fb.backup_finish_date, fb.is_copy_only,
db.backup_start_date, db.backup_finish_date, db.is_copy_only,
lb.backup_start_date, lb.backup_finish_date, lb.is_copy_only
FROM sys.databases d
OUTER APPLY
(SELECT TOP 1 backup_start_date, backup_finish_date, is_copy_only
FROM msdb.dbo.backupset WHERE database_name = d.name AND type = 'D'
ORDER BY backup_set_id DESC) fb
OUTER APPLY
(SELECT TOP 1 backup_start_date, backup_finish_date, is_copy_only
FROM msdb.dbo.backupset WHERE database_name = d.name AND type = 'I'
ORDER BY backup_set_id DESC) db
OUTER APPLY
(SELECT TOP 1 backup_start_date, backup_finish_date, is_copy_only
FROM msdb.dbo.backupset WHERE database_name = d.name AND type = 'L'
ORDER BY backup_set_id DESC) lb
ORDER BY CASE WHEN d.database_id <= 4 THEN 0 ELSE 1 END, d.name;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply