Tuesday, December 2, 2008

SQL Server 2008 – Backup Compression feature

To compress a backup specify COMPRESSION in the WITH clause of the backup command.

Here is an example:

BACKUP DATABASE YourDatabaseName

TO DISK = 'C:\YourDatabaseName.bak'

WITH INIT, COMPRESSION


Compressing a backup typically needs less I/O, because it is smaller so it increases the speed of the backup. But compression process consumes CPU usage.

By default backup compression is off. To enable it we can use the ‘backup compression default’ configuration.

Use master

Go

Exec sp_configure 'backup compression default', '1'

GO

RECONFIGURE

You don’t have to specify anything to restore the compressed backup

No comments: