In SQL Server 2008 Enterprise and later only, used by the BACKUP Transact-SQL statement to select the backup compression setting if a user does not specify WITH COMPRESSION or WITH NO_COMPRESSION.
Backup compression is supported for both disk backup devices and tape backup devices.
At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0.
By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).
The following example configures the server instance to create compressed backups by default.
USE master; GO EXEC sp_configure ‘backup compression default’, '1'; RECONFIGURE WITH OVERRIDE;