Get Average Backup Growth and Time Taken

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;

Happy scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

One thought on “Get Average Backup Growth and Time Taken”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s