SQL Server Backup Compression

Опубликовано: 19 Март 2026
на канале: DBATAG

8,637

Detailed Article for "SQL Server Backup Compression" is available at :

http://sqlserver-training.com/backup-...

Follow us at more Updates at
-----------------------------------------
Website : http://sqlserver-training.com/
Twitter :   / dbatag
Facebook :   / dbatag
Linked In :   / dbatag
================================

Backup compression was introduced in SQL Server 2008 Enterprise, which reduces the size of backups and the time that it takes to create them.

Using backup compression is a simple process. Transact-SQL—WITH COMPRESSION in the BACKUP statement,

BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:\AWBackupCompressed.bak' WITH COMPRESSION

By default, no backups are compressed. You can change this default setting by using sp_configure to set the value of the backup_compression_default setting as shown in the following code example. Alternatively, you can change the default setting by selecting the option in the Server Properties dialog box.

USE master
Go
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Although compressed backups require less disk space to store them and less time to create, there is a tradeoff. The compression is a CPU-intensive operation that can impact other operations that occur on your servers. To avoid such contention, you can use Resource Governor to limit the CPU usage for a backup operation.

Note:

Compression and transparent data encryption are mutually exclusive.
Creating a backup with compression is supported only in SQL Server 2008 Enterprise Edition; all SQL Server 2008 editions can restore a compressed backup.

Visit the following link for more details and SQL Server Tutorials

http://sqlserver-training.com/
http://sqlserver-training.com/how-man...
http://sqlserver-training.com/how-to-...
http://sqlserver-training.com/feature...
http://sqlserver-training.com/complet...
http://sqlserver-training.com/cumulat...
http://sqlserver-training.com/video-t...
http://sqlserver-training.com/how-to-...
http://sqlserver-training.com/dbcc-ch...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/migrati...
http://sqlserver-training.com/new-fea...
http://sqlserver-training.com/extensi...
http://sqlserver-training.com/databas...
http://sqlserver-training.com/data-co...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/microso...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/databas...
http://sqlserver-training.com/introdu...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/new-fea...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/sql-ser...
http://sqlserver-training.com/sql-azu...
http://sqlserver-training.com/can-we-...
http://sqlserver-training.com/master-...