I have this query to find the duration of backups taken which I want to run against registered servers in CMS across the entire enterprise.
The problem though, is how to find only the MIN
and MAX
duration per database rather than the runtime of all of the backups.
I've messed around a lot with MIN
and MAX
but have yet to find the correct combination.
Am I going about this the wrong way?
DECLARE @dbname sysname
SET @dbname = NULL
SELECT database_name AS [Database]
, backup_start_date AS [Backup Started]
, backup_finish_date AS [Backup Finished]
, backup_size AS [Backup Size]
, compressed_backup_size AS [Compressed Backup Size]
, CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
+ CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset
WHERE [type]!='L'
AND database_name IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name
,backup_start_date
,backup_finish_date
,backup_size
,compressed_backup_size;
Code sourced from here.