Where is the Default Settings for Backup Compression? – Interview Question of the Week #191

Question: Where is the Default Settings for Backup Compression?

Answer: I think before I continue more on this blog post, I must show you an image which has created this question.

Please look at the following database settings screen. You can go to this screen by right clicking on any particular database, go to tasks and select Back Up…

On this screen, you will see three options for Backup Compression. Quite a lots of people see this setting and wonder where actually the default server settings are.

Well, the server settings are visible by right-clicking on the server instance name. Go to Properties and select Database Settings.

Under the database settings screen, there is a section of Backup and restore where you will see a small checkbox of Compress backup. If you select this checkbox, it will change the default of the compressed backup settings.

You can also check the default backup compression settings by running following T-SQL.

SELECT
CASE [value]
WHEN 1 THEN 'Backup Compression On'
ELSE 'Backup Compression '
END AS [Backup Compression Default]
FROM sys.configurations
WHERE name = 'backup compression default'
GO

Additionally, if you want to enable or disable backup compression using T-SQL you can run the following script.

To set the default compression on by default, run following command.

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

To set the default compression off by default, run following command.

EXEC sys.sp_configure N'backup compression default', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

More than interview question, I think this is interesting information which every DBA should know about their server

Reference: Pinal Dave (https://blog.SQLAuthority.com)