SQL Server Health Check

Why Should You Enable SQL Server Database Backup Compression by Default?

Updated November 16, 20263 min read

Make backup compression your default. A thick stack of paper feeds into a rolling press and comes out as one thin sheet.

What is the SQL Server backup compression default option?

This setting determines whether SQL Server will compress backups by default or not.

Why does SQL Server backup compression matter?

If you do enable it, you can save 30-50% of the backup file size.

Everything in SQL Server becomes faster!

Since reading or writing from storage I/O is the slowest SQL Server component, you now need to write way less to disk, you need to read way less from disk during restore, you can keep more backup files in file rotation, etc.

Backup compression is not available in all editions of SQL Server.

Microsoft introduced compression in SQL Server 2008, and Standard Edition starting in SQL Server 2008R2.

Why should you enable backup compression?

Database backups will take up less space and will be faster.

CPU usage

Compression can increase CPU usage, but it’s not a big deal.

Most businesses have non-production time windows each night when processes such as SQL Server backups and maintenance occur.

Transaction log backups can be compressed too, with the same COMPRESSION option.

How can I change the default setting for SQL Server backup compression?

You can do this by using SQL Server Management Studio (SSMS):

  1. Right-click on the instance name in Object Explorer
  2. Choose Properties > Database Settings > and check if the Compress backup checkbox is selected.
Figure 1 - Database backup compression option
Figure 1 - Database backup compression option

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

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

To enable SQL Server backup compression by default using T-SQL, execute the following command:

T-SQL
EXEC sys.sp_configure N'backup compression default'
	,N'1' GO

RECONFIGURE
WITH OVERRIDE GO

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜