Backup Compression in SQL Server

Backup Compression in SQL Server 2008

Hi Friends, Yes I know this is very well known and something too old to post about; unless I received a mail today asking me about this. So here is something about Backup COmpression for people who are new to SQL Server 2008 or do not know about it.

Backup Compression is an Enterprise Edition feature only. You can get huge disk space savings upto 80%. However, compression ratio depends on multiple factors including but limited to type of data, consistency of data, whether the data is encrypted or not, etc..

Compressing a backup reduces I/O considerably but increases CPU usage significantly.

Take a normal full backup of Adventureworks and observe the backup size.

Then take another backup with compression (you can use the following code) and observe the size.

--
BACKUP DATABASE [AdventureWorksDW] 
TO DISK = N’C:\AD-DW.bak’ WITH NOFORMAT, NOINIT, 
NAME = N’AdventureWorksDW-Full Database Backup’, SKIP, 
NOREWIND, NOUNLOAD, 
COMPRESSION, 
STATS = 10
GO
--

when you take backups in the both the scenarios, try to use Performance monitor to observer the I/O counter and the CPU counter to observe the perforamnce tradeoff (IO vs CPU)

 

 

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published. Required fields are marked *