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
One thought on “Check Backup Encryption Sizes”