SQL Server’s built-in capability to compress backup files during the backup operation itself, reducing storage consumption and network transfer time without requiring third-party tools.
- Native backup compression was introduced in SQL Server 2008 and is available in Standard and Enterprise editions: It should be enabled by default in most environments.
- Typical compression ratios range from 50% to 80%: The actual reduction in backup file size depends on the type of data being backed up and its compressibility.
- Compression increases CPU usage during backup operations: In CPU-constrained environments, compressed backups should be scheduled during off-peak hours or the compression algorithm should be adjusted.
- Native compression and third-party compression tools should not be combined: Only one compression layer should be applied to avoid unnecessary overhead.
- Default backup compression can be configured at the server level using:
EXEC sp_configure 'backup compression default', 1; - Relevant Idera tools: SQL Safe Backup extends native compression with advanced algorithms that provide greater compression ratios and faster backup performance.
- Related terms: Backup, Full Recovery Model, Storage Optimization, SQL Safe Backup.
