SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James' SQL Footprint

Love SQL Server, Love life.


Leave a comment on the original post [jamessql.blogspot.com, opens in a new window]

Loading comments...