Export (0) Print
Expand All
Expand Minimize
2 out of 2 rated this helpful - Rate this topic

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. 

NoteNote

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 noteImportant

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;

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.