SQL Server 2008 – Database Backup Compression

Backup compression was introduced in SQL Server 2008 Enterprise.

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.

Backup Compress at Server Level (Thru Script)

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE

Backup Compress at Server Level (Thru UI)

SQL Server 2008 – Database Backup Compression

After this configuration all the backup on the server without any T-SQL backup code change were compressed backup.

Backup Compression at Database Level

You can change the backup compression behavior for an individual backup as

For Compression

BACKUP DATABASE myDB TO DISK='D:\Backup\MYDB.bak'
WITH COMPRESSION
GO

For No Compression

BACKUP DATABASE myDB TO DISK='D:\Backup\MYDB.bak'
WITH NO_COMPRESSION
GO

Hope this help you.

Share on Facebook

Post on X

Follow us

Save