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
MAX duration per database rather than the runtime of all of the backups.
I've messed around a lot with
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.