Why should you enable SQL Server database backup compression by default?

What is the SQL backup compression default option?

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

Why does SQL backup compression matter?

Because if you do enable it, you can save 30-50% of backup file size. Everything in SQL Server becomes faster! Since reading or writing from storage I/O is the slowest SQL 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 file 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.

By the way, this check is a part of our SQL Server Health Check service.

Why should you enable backup compression?

Database backups could take less space and will be faster.

CPU usage

Compression can increase CPU usage, but it’s not a big deal. Most businesses have a non-production time windows where they use each night and processes such as like sql backups and maintenance occur. Transaction log backups are not compressed by the way.

How to change sql backup compression default?

Using  SQL Server Management Studio (SSMS):

  1. Right-click on the instance name in Object Explorer
  2. Choose Properties > Database Settings > check if the “Compress backup” checkbox is selected.
Shows the location of compress backup flag in the Microsoft SQL Server Managment Studio,
Figure 1 – Database backup compression option.

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

  1. SELECT
  2. CASE [value]
  3. WHEN 1 THEN 'Backup Compression On'
  4. ELSE 'Backup Compression'
  5. END AS [Backup Compression Off]
  6. FROM sys.configurations
  7. WHERE name = 'backup compression default'

To set the default sql backup compression ON by default using T-SQL, run following command:

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

More information

Microsoft – BACKUP (Transact-SQL)
Microsoft – Backup Compression (SQL Server)

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *