Hello! Last week I shared a script that gives you the average backup growth over a month, and the rolling average throughout the month. I then realised that having the duration of the backup might also prove useful, so I’ve updated the script for below. As with the last script, it won’t work on version prior to SQL Server 2012 .If you try it on 2008 R2 and earlier you get a syntax error I’ve not seen before: “The Parallel Data Warehouse (PDW) features are not enabled.” More on this can be read on DBA Stack Exchange. You can work around this by backing up and restoring msdb onto a later edition of SQL Server, just make sure you take a copy only backup and that you rename the restored version.
SELECT [database_name] AS [Database] ,[backup_start_date] AS [Date] ,AVG([backup_size] / 1024 / 1024) AS [BackupSize_MB] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS [movingAvg] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) RANGE UNBOUNDED PRECEDING ) AS [Avg] ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, backup_start_date, backup_finish_date), 0), 108) AS TimeTaken FROM msdb.dbo.backupset WHERE [type] = 'D' AND [database_name] = 'AdventureWorks2012' GROUP BY [database_name] ,[backup_start_date] ,[backup_size] ,backup_finish_date ORDER BY [Date] DESC;