Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

James' SQL Footprint

Love SQL Server, Love life.

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

  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
    SUM(sizeMB) sizeMB,
    MAX(duration) duration,
    SUM(sizeMB)/MAX(duration) speedMBSEC
   FROM (
       bs.database_name AS DBNAME,
       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
  ) b
 GROUP BY  DBNAME,backup_type
 order by DBNAME asc, backup_type asc, AvgDurationSEC desc


Leave a comment on the original post [, opens in a new window]

Loading comments...