2

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.

2
  • what do you mean by "min" and "max"? Are you talking about the earliest backup taken and the latest backup taken?
    – Hannah Vernon
    Jan 20, 2017 at 22:39
  • The shortest and longest amount of time a backup took. Jan 20, 2017 at 22:46

1 Answer 1

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.