Backup Compression Default

By default, the option to compress backups is disabled in SQL Server. Enabling backup compression can significantly reduce the size of backup files and improve the speed of backups. However, not all SQL Server editions support backup compression and keep in mind that enabling compression will increase CPU usage during the backup process.

Why Backup Compression Is Important

  • Smaller Backup Files: Compression reduces the size of the backup, which saves disk space and reduces storage costs.
  • Faster Backups: Compressed backups are generally created faster since less data is written to disk.
  • Increased CPU Usage: Compression increases CPU usage during the backup process, which can affect system performance.

Suggested Action

If you are using an edition of SQL Server that supports backup compression, it is recommended to enable the backup compression default setting to ensure all backups use compression by default.

How to Enable Backup Compression for All Databases

To configure the backup compression default for all databases, use the following SQL command:

USE [master]
GO
EXEC sys.sp_configure @configname = N'backup compression default', @configvalue = N'1';
RECONFIGURE;
GO

How to Perform a Single Backup With Backup Compression

If you only want to enable compression for specific backups, use the following SQL command:

BACKUP DATABASE [WhippetWorks]
TO DISK = N'Z:\Backups\WhippetWorks.bak'
WITH COMPRESSION;
GO

This will compress the backup file for the database without changing the default setting.

How Aireforge Detects Backup Compression Settings

Aireforge checks whether backup compression is enabled by default, considering the SQL Server version and edition. Although all versions from SQL Server 2008 onwards can restore compressed backups, only certain editions, Enterprise, Standard, and Developer, can create compressed backups. Aireforge flags any instance where the backup compression default setting is disabled, providing recommendations based on the supported features of your SQL Server version.