SQL SERVER – Configure the Backup Compression Default Server Configuration Option

When I get a chance to talk to Enterprise customers working with a really smart DBA team, I generally look out for options they use on a daily basis and are often missed in the bigger scheme of things. One such feature of SQL Server Enterprise Edition is the ability to do Backup Compression. It is really a powerful feature and the fine prints is that – it is disabled by default. In this blog we will look at how to view or configure the backup compression default server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL. The backup compression default option determines whether the server instance creates compressed backups by default. When SQL Server is installed, the backup compression default option is off.

Changing the setting using SSMS

To configure this setting, use the following step.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Database settings node.
  3. Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:
    • If the Compress backup box is blank, new backups are uncompressed by default.
    • If the Compress backup box is checked, new backups are compressed by default.

SQL SERVER - Configure the Backup Compression Default Server Configuration Option compression-setting-default-01

If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

TSQL – Steps to configure backup compression default option

The T-SQL command to achieve the same will be as described below:

  1. Connect to the Database Engine.
  2. From the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the server instance to create compressed backups by default.

EXEC sp_configure 'backup compression default', 1;

If you want to find out if backup compression is enabled by default, then we can use the following TSQL command to find the same.

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

This example queries the sys.configurations catalog view to determine the value for backup compression default. A value of 0 means that backup compression is off, and a value of 1 means that backup compression is enabled.

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

SQL Backup and Restore
Previous Post
SQL SERVER – What’s the Difference between ROW_NUMBER, RANK, and DENSE_RANK? – Notes from the Field #096
Next Post
SQL SERVER – How to Enable or Disable All the Triggers on a Table and Database?

Related Posts

8 Comments. Leave new

  • Dear Sir, When I am checking this option, it is getting unchecked automatically once I close this window. Secondly, we have a job scheduled for automatic backup process. Does this option work for scheduled jobs as well for backup?

  • Thanks, I find it slightly annoying that it’s off by default, obviously easy to create a backup with a compressed option set.

  • I wonder why this isn’t enabled by default. It’s something I always change, but it’s always puzzled me why one wouldn’t use it.

  • Shivendra Kumar Yadav (MCP)
    September 4, 2015 7:36 pm

    Nice Description Sir,
    Defiantly! Backup compressions are very much use full in terms of storage.
    An Enterprise production implementation perspective, we also need to think about CPU consumption while taking the backups. Because compression is nothing but finding similar stuffs and keep the references of it and use that while decoding (restore) process, which consumes a high level of CPU in that duration (Here I am talking about Full backups instead of other ones.).
    So when we apply the Instance level compression with big bunch of heavy databases then we also thing about CPU utilization in that time.


Leave a Reply