March 13, 2014 at 1:15 pm
From backupset and backupmediafamily system table, I am trying to get the last full backup of a database. along with other details like
Dbname, last backup date, days passed since last full backup, and backup location.
I cant join both system tables, because I am using max(finishdate)
March 14, 2014 at 4:23 am
I've created a list of the last backups for each database and join this with the [backupset] to get the associated "media_set_id". I've put this query inside a CTE so I can easily reference this set multiple times, once for each type of backup. This final query is joined to the [backupmediafamily] table to get the associated filenames.
;WITH CTE_Last_backup_list AS
(
SELECT
Last_backup.database_name
, Last_backup.type
, Last_backup.last_backup_date
, media_set_id
FROM msdb.dbo.backupset
INNER JOIN
(SELECT
backupset.database_name
, type
, max(backupset.backup_finish_date) as last_backup_date
FROM msdb..backupset
group by
backupset.database_name
, type
) Last_backup
ON backupset.database_name = Last_backup.database_name
AND backupset.type = Last_backup.type
AND backupset.backup_finish_date = Last_backup.last_backup_date
)
SELECT db.NAME as Database_name
, db.recovery_model_desc as Recovery_Model
, bs_full.last_backup_date as last_FULL_backup
, media_full.physical_device_name as FULL_backup_file
, bs_log.last_backup_date as last_LOG_backup
, media_log.physical_device_name as LOG_backup_file
FROM sys.databases db
LEFT OUTER JOIN CTE_Last_backup_list as bs_full
ON db.name = bs_full.database_name
AND bs_full.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily media_full
ON bs_full.media_set_id = media_full.media_set_id
LEFT OUTER JOIN CTE_Last_backup_list as bs_log
ON db.name = bs_log.database_name
AND bs_log.type = 'L'
LEFT OUTER JOIN msdb.dbo.backupmediafamily media_log
ON bs_log.media_set_id = media_log.media_set_id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply