Setting : Verify and enable backup compression using T-SQL.

Backup compression was an added feature starting with SQL Server 2008 and this only comes with enterprise edition. But the later versions supports backup compression from standard editions.

Following are the steps to verify whether the backup compression is enabled or not and how to enable / disable it.

 To check current backup compression setting :

SELECT *
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO

When Not Enabled :

When Enabled :

To Enable Backup Encryption:

 EXEC sp_configure 'backup compression default', 1 ;
 RECONFIGURE WITH OVERRIDE ;
 GO

To Disable Backup Encryption:

 EXEC sp_configure 'backup compression default', 0 ;
 RECONFIGURE WITH OVERRIDE ;
 GO