Backup duration statistics

Here is a simple query which can list backup duration statistics for all database, including the max, min, avg of the backup duration.  you can estimate the backup duration by this query when you plan your backup job



 SELECT
  DBNAME,
  backup_type,
  MAX(sizeMB) as MaxBackupFileSizeMB,
  Max(duration) as MaxDurationSEC,
  MAX(speedMBSEC) as MaxSpeedMBperSEC,
  MIN(sizeMB) as MinBackupFileSizeMB,
  MIN(duration) as MinDurationSEC,
  MIN(speedMBSEC) as MinSpeedMBperSEC,
  AVG(sizeMB) as AvgBackupFileSizeMB,
  AVG(duration) as AvgDurationSEC,
  AVG(speedMBSEC) as AvgSpeedMBperSEC
 FROM
  (
   SELECT
    backup_set_id,
    DBNAME,
    backup_type,
    SUM(sizeMB) sizeMB,
    MAX(duration) duration,
    SUM(sizeMB)/MAX(duration) speedMBSEC
   FROM (
    SELECT
       bs.backup_set_id,
       bs.database_name AS DBNAME,
       bs.backup_start_date, 
       bs.backup_finish_date,
       DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) duration,
       CASE bs.type 
        WHEN 'D' THEN 'Full' 
        WHEN 'L' THEN 'Log' 
        WHEN 'I' THEN 'Diff'
       END AS backup_type, 
       bs.backup_size/1048576 sizeMB
    FROM   msdb.dbo.backupmediafamily  bmf
       INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
    Where DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) >= 1
    ) a
   GROUP BY
    backup_set_id,
    DBNAME,
    backup_type
   
  ) b
 GROUP BY  DBNAME,backup_type
 order by DBNAME asc, backup_type asc, AvgDurationSEC desc