SQL 2008 Backups: Compression Comparison and Notes

This content 8 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

width=180
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.

Speak Your Mind

*

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