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.
- In Object Explorer, right-click a server and select Properties.
- Click the Database settings node.
- 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.
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:
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- 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;
RECONFIGURE WITH OVERRIDE;
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.
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)
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?
What is the edition of your SQL Server instance?
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.
My theory is – its of because it takes more CPU than regular backups.
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.
Very true! Thanks for adding a comment.