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):
- Right-click on the instance name in Object Explorer
- Choose Properties > Database Settings > check if the “Compress backup” checkbox is selected.
You can also check the default backup compression settings by running following T-SQL.
SELECTCASE [value]
WHEN 1 THEN 'Backup Compression On'
ELSE 'Backup Compression'
END AS [Backup Compression Off]
FROM sys.configurations
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.
