SQL Server – Find MIN and MAX Database Backup Duration

backupsql servert-sql

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.

Best Answer

Use the ROW_NUMBER() aggregate to get the min and max rows, then show only those rows:

;WITH cte AS (
    SELECT d.name
        , bs.backup_start_date
        , bs.backup_finish_date
        , DurationInSeconds = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        , RnMin = ROW_NUMBER() OVER (PARTITION BY d.name 
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ASC)
        , RnMax = ROW_NUMBER() OVER (PARTITION BY d.name 
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) DESC)
    FROM msdb.dbo.backupset bs
        INNER JOIN master.sys.databases d ON bs.database_name = d.name
)
SELECT cte.name
    , cte.backup_start_date
    , cte.backup_finish_date
    , cte.DurationInSeconds
FROM cte
WHERE cte.RnMax = 1
    OR cte.RnMin = 1
ORDER BY cte.name
    , cte.backup_start_date;
Related Question