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.

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 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)

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.