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):
- Right-click on the instance name in Object explorer
- Choose properties, Database settings, and check if the “Compress backup” box is selected.
You can also check the default backup compression settings by running following T-SQL.
SELECTCASE [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)
