How to enable compression on all SQL Server Database Backups

Backup compression is not something new to SQL Server, it has been there with few versions. However, many are unaware on setting the default compression on all backups, not explicitly for specific backups.

How can we instruct SQL Server to compression all backups by default?

There is a server configuration called backup compression default that can be set at the instance level. If you enable it, all backups will be compressed.

Check the following code;

-- Taking a backup before enabling compression
BACKUP DATABASE AdventureWorks2014
 TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks_BeforeCompressionEnabled.bak'
 WITH INIT;
GO

-- Enabling compression
EXEC sp_configure 'backup compression default', 1;
GO
RECONFIGURE;
GO

-- Taking a backup after enabling compression
BACKUP DATABASE AdventureWorks2014
 TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks_AfterCompressionEnabled.bak'
 WITH INIT;
GO