asked
backupset metadata retrieve - start/end date specific
Hi all, Wondering if someone can help. I've been tasked with getting the metadata for the backups that have run on all our db's as far back as they'll go. I've got that but the company doesn't want to go that granular so they've asked for the same but just the oldest and newest date? I know it's something to do with my inner joins perhaps. Would appreciate some input if possible. The query I have used to retrieve and (nearly) get the info I need is: SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 365) ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date; Mike
Close. This might be a bit closer (without using JOINs): WITH backupinfo AS (SELECT bs.database_name, bs.backup_start_date, bs.backup_finish_date, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, bs.backup_size, ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY backup_start_date) AS ascending, ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY backup_start_date DESC ) AS descending FROM msdb.dbo.backupset bs WHERE (CONVERT(DATETIME, bs.backup_start_date, 102) >= GETDATE() - 365) ) SELECT backupinfo.database_name, backupinfo.backup_start_date, backupinfo.backup_finish_date, backupinfo.backup_type, backupinfo.backup_size FROM backupinfo WHERE ascending = 1 OR descending = 1 ORDER BY backupinfo.database_name, backupinfo.backup_type, backupinfo.backup_start_date; What I've done is wrap up most of your original query in a CTE (Common Table Expression / WITH clause), and added a couple of extra columns - in effect an ascending and descending order by for each group of database/backup type, sorting by backup start date. Then all I do is select from the CTE where either of those row numbers is 1 - thereby giving the oldest and newest Log & Data backup for each database. It's a start, anyway. **edit** They want seconds as well? Add in this snippet to the main SELECT: DATEDIFF(SECOND, backupinfo.backup_start_date, backupinfo.backup_finish_date) AS Seconds, If they want it formatting prettily, then that's a little more complicated... **2nd edit** ...and here's that little-more-complicated answer: WITH backupinfo AS (SELECT bs.database_name, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS Seconds, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, bs.backup_size, ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY backup_start_date) AS ascending, ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY backup_start_date DESC ) AS descending FROM msdb.dbo.backupset bs WHERE (CONVERT(DATETIME, bs.backup_start_date, 102) >= GETDATE() - 365) ) SELECT backupinfo.database_name, backupinfo.backup_type, backupinfo.backup_start_date, backupinfo.backup_finish_date, Seconds, RIGHT('00' + CONVERT(VARCHAR(2), backupinfo.Seconds / 3600), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2), (backupinfo.Seconds % 3600) / 60), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2), backupinfo.Seconds % 60), 2) AS Duration, backupinfo.backup_size FROM backupinfo WHERE ascending = 1 OR descending = 1 ORDER BY backupinfo.database_name, backupinfo.backup_type, backupinfo.backup_start_date; Added the seconds into the CTE, and doing an 'orrible formatting with integer/remainder divisions. Seems to be OK on the limited test data I've thrown at it.
18 People are following this question.
