Check Backup Encryption Sizes

I had a recent project to enable backup encryption on all our servers.  Then question from the storage team came up will this required additional space.  Well by then I had already enabled in all our test servers so I wrote a query that would compare the average size of backups before encryption to after encryption.  Keep in mind we do keep only two weeks of history in our backup tables so this is a fair comparison.  If you don’t have maintenance tasks to clean up your backup history then you should have backup_start_time to the where clauses to get more accurate numbers and setup a maintenance tasks to keep your msdb backup history in check.

SELECT e.type,
	u.avg_size u_avg_size,
	e.avg_size e_avg_size,
	(e.avg_size - u.avg_size) diff_size
FROM (
	SELECT bs.type,
		AVG(bf.backup_size)/1024/1024/1024 avg_size
	FROM msdb.dbo.backupfile bf
		INNER JOIN msdb.dbo.backupset bs ON bs.backup_set_id = bf.backup_set_id
		INNER JOIN msdb.dbo.backupmediaset mds ON bs.media_set_id = mds.media_set_id
	WHERE mds.is_encrypted IS NULL 
	GROUP BY bs.type) u 
	INNER JOIN 
		(SELECT bs.type,
			AVG(bf.backup_size)/1024/1024/1024 avg_size
		FROM msdb.dbo.backupfile bf
			INNER JOIN msdb.dbo.backupset bs ON bs.backup_set_id = bf.backup_set_id
			INNER JOIN msdb.dbo.backupmediaset mds ON bs.media_set_id = mds.media_set_id
		WHERE mds.is_encrypted = 1
		GROUP BY bs.type) e ON e.type = u.type

Related Posts

One thought on “Check Backup Encryption Sizes

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.