Why You Should Enable Database Backup Compression by Default

Why You Should Enable Database Backup Compression by Default

What Is the Backup Compression Default Option?

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

Backup compression isn’t available in every version of SQL Server. Microsoft introduced it in SQL Server 2008, and in Standard Edition starting with SQL Server 2008 R2.

Why should you enable it?

Database backups can take up less space and complete faster.

CPU usage

Compression can increase CPU usage, but this usually isn’t a big concern.
Most companies have a non-production window every night when backups and maintenance run. Also, transaction log backups aren’t compressed, so there’s generally little reason to worry about it.

How do you fix it?

Using SQL Server Management Studio (SSMS):

1. Right-click the instance name in Object Explorer
2. Select Properties, then Database settings, and check whether the “Compress backup” box is selected.

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

SELECT

CASE [value]

WHEN 1 THEN 'Bkp Compression On'

ELSE 'Backup Compression'

END AS [bkp Compression off]

FROM sys.configurations

WHERE name = 'backup compression default'

To turn on default compression using T-SQL, run the following command:

EXEC sys.sp_configure N'backup compression default', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO