SQL 2008 Backups: Compression Comparison and Notes
Here’s a transact-SQL to show you a comparison of a compressed and uncomopressed backup from your SQL server instance. Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. (Reference)
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.