backup compression default Option
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.
Note
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.
Important
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).
Examples
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;