Hello!

(There is an updated version of this script here that includes duration of the backup)

Departing from the scripts I have been sharing all week about the SQL Agent job, the script below gives you the day average of your backup and both the average and the rolling average of the size of the database backups throughout the month. This script will only work on 2012 and later. 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. The workaround is to take a copy only backup of the msdb database and restore to a SQL 2012/2014 instance (renaming the db to something else of course) and then running the query.

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]
FROM msdb.dbo.backupset
WHERE [type] = 'D'
AND [database_name] = 'AdventureWorks2012'
GROUP BY [database_name]
,[backup_start_date]
,backupset.backup_size
ORDER BY [Date] DESC;

Happy scripting!