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.

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

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

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

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.

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

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)

Compression, SQL Backup, SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190
Next Post
How to Escape a Single Quote in SQL Server? – Interview Question of the Week #192

Related Posts

2 Comments. Leave new

  • Wouldn’t you want to add the word “Off”?

    ELSE ‘Backup Compression Off’

    Reply
  • How much % enabling this setting would compress the SQL server db backups ? How about using open source 3rd party tool of HYPER-V backup compression ? What is the difference in output ?

    Reply

Leave a Reply