Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating