2 out of 2 rated this helpful - Rate this topic

backup compression default Option

SQL Server 2008 R2

SQL Server 2008

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).

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;

Reference

BACKUP (Transact-SQL)

RECONFIGURE (Transact-SQL)

sp_configure (Transact-SQL)

Concepts

Setting Server Configuration Options

Backup Overview (SQL Server)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD