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

Category: Reliability
Item: Backups compression settings

What is the backup compression default option?

It determines whether the server instance creates compressed backups by default.

Backup compression is not available in all editions of SQL Server. Microsoft introduced in SQL Server 2008, and Standard Edition starting in SQL Server 2008R2.

Why should you enable it?

Database backups could take less space and be faster.

CPU usage

Compression can increase CPU usage, but it’s not a big deal.
Most companies have a non-produce window they use each night where things like backup and maintenance occur. Also, transaction log backups are not compressed, so usually, there is no reason to be worried.

How to fix it?

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” box 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 'Bkp Compression On'
  4. ELSE 'Backup Compression'
  5. END AS [bkp Compression off]
  6. FROM sys.configurations
  7. WHERE name = 'backup compression default'

To set the default 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)

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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