How to do Backup compression in SQL Server?
The backup compression option determines whether SQL Server creates compressed or uncompressed backups .Backup compression option is off by default in SQL Server.The default behavior can be modified by sp_configure option “backup compression default” .
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;
To override the backup compression:
You can change the backup compression behavior for an individual backup by using WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement. We cannot take compressed and non-compressed backups on the same file. If we take COMPRESSION backup on a file were already non-compressed backup has taken,error will be shown.So we have to use different files for compressed and uncompressed backup.
Example to take backup for AdventureWorks with NO_COMPRESSION:
BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uncompressed.bak’ WITH NO_COMPRESSION
Example to take backup for AdventureWorks WITH COMPRESSION:
BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\compressed.bak’ WITH COMPRESSION
To calculate the compression ratio of a backup:
After taking backup with compression and without compression the backup_size can be compared to see the difference.
select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %” from msdb..backupset