SQL 2008 Backups: Compression Comparison and Notes


SELECT b.database_name ‘Database Name’, CONVERT (BIGINT, b.backup_size / 1048576 ) ‘UnCompressed Backup Size (MB)’, CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) ‘Compressed Backup Size (MB)’, CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / CONVERT (FLOAT, b.compressed_backup_size))) ‘Compression Ratio’, DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) ‘Backup Elapsed Time (sec)’ FROM msdb.dbo.backupset b WHERE DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 AND b.backup_size > 0 ORDER BY b.backup_finish_date DESC

You can also find out compression ratio with this transact-SQL code (Reference):

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Determine if you have backup compression enabled on a database:

USE AdventureWorks2012 ; GO SELECT value FROM sys.configurations WHERE name = ‘backup compression default’ ; GO

Configure backup compression and enable it on a database:

USE AdventureWorks2012; GO EXEC sp_configure ‘backup compression default’, 1 ; RECONFIGURE WITH OVERRIDE ; GO

Alternatively, you can use the WITH NO_COMPRESSION or WITH COMPRESSION in your BACKUP statement to override the database default.

Published At
Tagged with