THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Backup compression in SQL Server 2008

Having a few moments to spare, I decided to give this a spin.

Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. For example:

BACKUP DATABASE Adventureworks 
TO DISK = 'C:\Advc.bak' 
WITH INITCOMPRESSION

For fun, I compared backup file size and backup time between compressing and not compressing. I ran below after priming the cache (not that it should matter since backup read pages from disk, see http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/03/does-backup-utilize-pages-in-cache.aspx). I also deleted the backup files, if they exist, before execution.

DECLARE @dt datetime

SET @dt GETDATE()
BACKUP DATABASE Adventureworks 
TO DISK = 'C:\Adv.bak' 
WITH INIT
SELECT DATEDIFF(ms@dtGETDATE())

SET @dt GETDATE()
BACKUP DATABASE Adventureworks 
TO DISK = 'C:\Advc.bak' 
WITH INITCOMPRESSION
SELECT DATEDIFF(ms@dtGETDATE())

Size of backup file is 129 MB vs. 35 MB. Of course, compression ratio varies depending on what type of data there is in the database (string data tend to compress better than other data,. for instance). Time to execute commands were 7.5 seconds vs. 3.8 seconds. Now, this is a virtual machine on VPC and Adventureworks is a tiny database. But at least we can see clear potential for savings here.

So how do we know if a database backup was compressed? If you've used RESTORE HEADERONLY, you probably noticed this annoying column called "Compressed". Well, this is the first time we see a value of "1" in that column.

It seems we cannot mix compressed and non-compressed backups on the same file, though. (Not that I often have multiple backups on the same file.) If I append a backup using COMPRESSION on a file were there already are non-compressed backup, I get an error. Or I do a backup without COMPRESSION on a file where there are compressed backups, the new backup will be compressed (even when not saying COMPRESSION). Something to look out for if you have several backups on the backup files.

So what about the RESTORE command? Well, there's nothing to say, really. You don't have to specify in the RESTORE command that the backup was compressed. Time for RESTORE was 10.4 vs 6.3 seconds (with the destination database already existing). I expect the difference to be bigger on real installation and realistic db size.

Published Wednesday, December 12, 2007 5:52 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jerryhung said:

Just want to clarify

RESTORE from "uncompressed" backup is 10.4 seconds

RESTORE from "compressed" backup is 6.3 seconds?

Interesting. I would have thought it takes some work to "uncompress" (longer), but the size is also smaller (shorter). Maybe they cancel out?

I am also curious to how this compares to SQLLiteSpeed from Quest, if you have it handy for comparison.

Or whether LiteSpeed will no longer be useful in SQL 2008....

December 12, 2007 12:53 PM
 

TiborKaraszi said:

Yes, restore from compressed was the faster one. I guess that what we gain from less I/O overshadows the higher CPU usage. This could probably change in the future as some installation move to solid state disks (much faster disks).

I haven't done any comparsion with existing products, and I don't think such comparsion would be useful until one has a proper setup (which I don't have now - using VPC). Also, sone should remember that other products has additional functionality... :-)

December 12, 2007 12:57 PM
 

Denis Gobo said:

>>Also, sone should remember that other products has additional functionality... :-)

yes LiteSpeed offers encryption

December 12, 2007 12:58 PM
 

Knyazev Alexey said:

EXEC sp_configure 'backup compression default', 1

RECONFIGURE

GO

BACKUP DATABASE Adventureworks

TO DISK = 'C:\AdvcAUTO.bak'

WITH INIT

December 13, 2007 12:48 AM
 

TiborKaraszi said:

That is cool (the sp_configure setting). I was thinking about this yesterday evening (after turning off the PC) and figured that there should be an sp_configure setting for this. This so we don't get into the "not supported by GUIs like maint plan" situation... :-)

December 13, 2007 1:44 AM
 

AaronBertrand said:

I'm not so sure I like the idea of setting the default behavior.  I prefer explicitly saying so in the statement (which shouldn't affect a whole lot of code, in most cases).  Someone else who comes along and takes a backup manually may not know that he/she is getting a compressed backup.

December 13, 2007 7:11 AM
 

AaronBertrand said:

Then again, I guess it doesn't really matter that they are getting a compressed backup.  And that's what they get for manually backing up anyway (without first checking).

December 13, 2007 7:43 AM
 

TiborKaraszi said:

I hear you , Aaron (not neccesarily appreciating changing default behavior). I was mostly thinking about users relying on tools and GUIs.

Consider Maint Plans. A lot of people uses them, but currectly you can't specify the CHECKSUM option for such backups. Having a default setting for this means we don't have to fight those tool developers to include these type of settings.

Come to think of it, I just filed a connect entry for an sp_configure to set backup CHECMSUM by default:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=317285

December 13, 2007 12:14 PM
 

Bart Czernicki said:

Creating compressed backups can ONLY be done from Developer/Enterprise.  However, 2008 Standard can restore a compressed backup.

Don't understand why they did this and essentially made it a ent. only feature (since if ur restoring an Ent db, u probably already have ent only features).  SQL 2005 SSAS includes compression for abf files and its included for both Standard/Enterprise versions of SSAS.

Go figure another thing MS does that doesn't make sense :) (at least to me)

December 13, 2007 2:25 PM
 

Linchi Shea said:

As far as I'm concerned, they should just make the compressed backup the 'native' backup and change the name of the current native backup to bloated backup (or something to that effect), well, unless there are undesirable side effect in this change that we don't yet realize.

After all, a backup is a backup. If you want a bloated backup, you should really make an effort to do so.

December 15, 2007 12:49 PM
 

Andrew Kelly said:

Linchi,

The potential undesirable side effect is increased CPU to do the compression. While the decrease in backup time and less I/O should outweigh the increase in CPU as always this depends on the situation.

December 16, 2007 7:25 PM
 

Linchi Shea said:

Andy;

I was being a bit facetious. Obviously, this feature needs to be put through much more rigorous and comprehensive tests before we are completely comfortable with its do's and don'ts. But I'm betting and hoping that with 16-core machines fast becoming staple offering in the data center, compressed backups will be the default.

December 19, 2007 11:00 AM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

HeatherK said:

I assume the sp_configure option affects the backups taken by log shipping.  Does it affect the database mirroring traffic?

January 10, 2008 9:17 AM
 

TiborKaraszi said:

It should be easy to trace the backup command used by the built-in support for log shipping. If the BACKUP command doesn't say anything about compression, then sp_configure would rule.

As for mirroring, then I'd guess no, since mirroring is separate from backup. But another new feature of 2008 is compression for the mirrored trafic. This is from memory, but I'm pretty certain I've read it somewhere... :-)

January 10, 2008 4:29 PM
 

Matt said:

I have just done some comparisons of SQL 2K8's compressed backups vs. Litespeed.

This was using SQL Server 2008 Nov2007CTP on Virtual PC and Litespeed Eval Copy 4.8.4.

Backing up the AdventureWorks DB which is 175Mb to disc:

SQL Native Uncompressed - 175Mb 38 secs

SQL Native Compressed - 40.5Mb  17 secs

LiteSpeed Compression Level 1 - 44Mb 9 secs

LiteSpeed Compression Level 2 - 35Mb 17 secs

I think it compares well with LiteSpeed for simple backups in terms of speed/compression but the lack of options (compression levels, affinity, thread etc, means it is difficult to tune for large backup operations.  LiteSpeed also supports encrypted backups.

January 21, 2008 7:51 AM
 

TiborKaraszi said:

To some extent, I don't mind 3:rd party product offering more options etc. I believe that a living 3:rd party market only increses the market potential for a product. :-)

January 22, 2008 12:45 PM
 

James said:

I had a chance to play with litespeed as well.

Very smooth compression!

Here is the link to this solution for everybody who haven't seen it yet:

http://www.scriptlogic.com/products/litespeed/

February 1, 2008 11:22 AM
 

Jeffrey Aven said:

Another third party solution which hasn't got a mention here is HyperBac, Quest should not get all the airtime here...

www.hyperbac.com/sqlserver/

supports compression, encryption, object recovery, developed by several of the original LiteSpeed developers

February 22, 2008 12:27 AM
 

Paul Z said:

Can you give a description/layout of the machine you used to run the compression test?  Is all of the data on 1 drive?  multiple drive?  how many drives? how many CPUs?  how much memory? ...

September 12, 2008 1:47 PM
 

TiborKaraszi said:

Paul Z,

You might want to say to whom you pose the question... I first thought you asked me, but re-reading my text I did say small database and VPC - si my aim was not an attempt to quantify how much data we can push or so, it was only a *relative* comparsion between non compressed and compressed backups. Also, the test was performed 2007-12, so it was on some CTP, perhaps 4 or 5. Anyhow, the machine was Core2 DUO, I believe 2.6 GHz, VPC 3, 32 bit host OS, 4 gb mem host os and 1GB mem to guest os. 2 250GB SATA disks in RAID0. Database was 2005's adventureworks. I doubt most of these are relevant, though...

September 13, 2008 7:09 AM
 

Douglas said:

Hi There,

I used Hyperbac for SQL Server to do backups and it works really well! The backups are pretty easy and don't use any stored procs, and work out of process (outside SQL Server memory space). Also all my existing native backup scripts work without any modification (if I add the .bak extension to the Hyperbac confi manager).

Performance is comparable and matches Litespeed and comes without the expensive pricing....

Regards,

Doug

August 4, 2009 9:55 AM
 

Stefano said:

Thanks Jeffrey for mentioning HyperBac.  I checked it out and assuming that it has a price advantage to the competition it's a steal.  I really like the architecture of the solution and the love the HyperBac Online option that allows you to run the database off the compressed copy!  Very impressive indeed.

September 22, 2009 3:12 PM
 

John Sterrett said:

The following are my findings of an real-world example with compression for databases build in a web development shop.

I have noticed a compression range from 65% to 75%.

http://johnsterrett.com/2009/04/15/backup-compression-with-sql-server-2008/

November 1, 2009 12:10 PM
 

Brett Hawton said:

You may also want to take a look at the new SQL Safe from Idera, http://www.idera.com/Products/SQL-Server/SQL-safe-backup/ It compresses the backup far more than any other product (or indeed the native compression) and Idera's other product, SQL Server Virtual Database, http://www.idera.com/Products/SQL-Toolbox/SQL-virtual-database/ allows a DBA to attach the backup file as a live database to which any TSQL command can be run using any tool such as SSMS Query Analyzer etc.

November 9, 2009 6:31 PM
 

Alexey Kucherenko said:

Please take a look at this tool: SQL Backup and FTP, http://sqlbackupandftp.com It's very easy to learn and use tool. It allows you to make a scheduled backups. It has free and commercial licenses. So, you're welcome

April 27, 2010 11:32 AM
 

Paul said:

Litespeed, Redgate and all others with compression offered HORRIBLE restore times when the backup was compressed.

May 12, 2010 12:00 PM
 

Paul said:

I tested with a large, DW DB (250GB).

May 12, 2010 12:02 PM
 

Siraj said:

Hi Mr TiborKaraszi,

I want compression back with script,Please give me details of script mode in sqlserver

              Thanx

May 3, 2011 3:28 AM
 

Rajeev said:

I am using enterprise edition of sql server 2008...When i am using above query it gives an error...."BACKUP DATABASE WITH COMPRESSION is not supported on Standard Edition."....please give me solution..

May 26, 2011 4:41 AM
 

Rajeev said:

I am using standard edition of sql server 2008...When i am using above query it gives an error...."BACKUP DATABASE WITH COMPRESSION is not supported on Standard Edition."....please give me solution..

May 26, 2011 4:43 AM
 

Miek Weaver said:

I was wondering if you backup one database to four files with Compression, will that not also decrease Backup time.  

I have a 300 gig database that I am now backing up to 4 files.

I have seen the backup drop more 75% in the time it takes to complete.

Now, if I added compression would I increase backup speed even more?

June 22, 2011 8:07 AM
 

Mike Weaver said:

Test my question adn yes the backup is faster with 4 file and compression

June 22, 2011 9:43 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement