SQL Server - How to use SQL Backup Compression

27 June,2012 by Jack Vamvas

There was a lack of space on a destination drive, so I decided to implement SQL Server 2008 Backup Compression . The aim was to decrease the size of the storage and fit the backup file onto the drive.

The usage of COMPRESSION at the BACKUP phase is distinct from Data compression in SQL Server 2008

By default SQL Server 2008 has backup compression turned off. Check sp_configure. Adding the COMPRESSION keyword , triggers the COMPRESSION process.

You’ll notice some increased CPU usage, so be careful with COMMPRESSION during high usage times.

The size impact of the database compression will depend on the data type. For example, compressing string data types  is more effective then numbers. 

A quick test of the same database indicated the level of storage space saving. The same database was backed up on both occassions. I use the Copy ONLY backup in SQL server and not break the backup chain

BACKUP DATABASE  SQLServerDB
TO DISK = 'N:\SQLServerDB.bak'  
WITH INIT,COPY_ONLY


--The backup file was 18GB


BACKUP DATABASE   SQLServerDB
TO DISK = 'N:\SQLServerDB_1.bak'  
WITH INIT,COMPRESSION,COPY_ONLY

--The backup file was 6.02 GB

 Read More

SQL Server – Differential Backup - SQL Server DBA

SQL Server backup review with Powershell - SQL Server DBA

Copy only backup in SQL server 2005 and not break the backup chain


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - How to use SQL Backup Compression


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer