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” .

Syntax:

USE master;
GO
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
GO

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
GO

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.

Syntax:

select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %”   from msdb..backupset