Average Backups and Their Growth

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!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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