What is the SQL Server backup compression default option?
This setting determines whether SQL Server will compress backups by default or not.
Why does SQL Server backup compression matter?
If you do enable it, you can save 30-50% of the backup file size.
Everything in SQL Server becomes faster!
Since reading or writing from storage I/O is the slowest SQL Server 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 files 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.
Why should you enable backup compression?
Database backups will take up less space and will be faster.
CPU usage
Compression can increase CPU usage, but it’s not a big deal.
Most businesses have non-production time windows that they use each night and processes such as SQL Server backups and maintenance occur.
Transaction log backups are not compressed by the way.
How can I change the default setting for SQL Server backup compression?
You can do this by 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 checkbox is selected.
You can also check the default backup compression settings by running the following T-SQL:
SELECT CASE [value]
WHEN 1
THEN 'Backup Compression On'
ELSE 'Backup Compression'
END AS [Backup Compression Off]
FROM sys.configurations
WHERE name = 'backup compression default'To enable SQL Server backup compression by default using T-SQL, execute the following command:
EXEC sys.sp_configure N'backup compression default'
,N'1' GO
RECONFIGURE
WITH OVERRIDE GOMore information
Speak with a SQL Expert
In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide operational peace of mind
