SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)

  • Great post!

    Question: Is this feature available with Std Edition or only Enterprise?


  • SQL 2008 does still support this feature of splitting the Backup over multiple files.

    During Backup and compression it was writting to each of the 5 files in a series basis and I am not sure how multiple procs and disks would affect, imporve this?

    The compression was approx 190MB per each of the 5 files - meaning that the 980MB was split amongst the 5 files.

    (Time wise it seemed to take longer a minute or two - but I would need to confirm this)



  • I think this is an Enterprise feature only

  • Very interesting numbers. Since I have to ship backup files across a low-speed network, this could come in very useful.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes, in SQL 2008 - the Database Mirrioring and Log Shipping* also benefit from this compression technology built into the engine.

    I am not sure if this is default or a setting for the above, but I will investigate and provide more details in future articles.



  • Nicely done article. However, we can't assume linear performance on larger databases - 100GB or 10 TB. (Is 100 GB still considered large? I remember considering 30 MB huge, but that's a long time ago).

    I've got the 100GB databases, but no time to install 2008 and report back, I'm afraid.

    Certainly, we've always got to test these days - so many old rules of thumb about relative speed of the parts of the IO chain are different than once upon a time. I've tended to avoid compression: I can write to a good iSCSI array faster than the CPU can do the compression. But compression keeps improving as well.


    Roger L Reid

  • Hi

    I have a 109GB, 110GB, 111GB.. Database that I am in the process of testing with and I will be doing a follow-up article - possibly in more technical detail.

    I will post to this forum and possibly in a follow-up article on the findings



  • It would be interesting to compare the backup restore vs. the current backup champ Redgate SQL Backup........

    Also, as the forum sponsor, any word on when Redgate will have a 2008 product?

    Rick Sheeley, BannerAMC

  • This is an Enterprise only feature for 2008. That could change in the future.

    Restore times should be much quicker, similar to backup time savings. Less reading from disk (slow) and quick decompression in memory.

    Red Gate has committed to 2008 support when 2008 ships. The products are being tested against 2008 versions now, but the 2008 code is not feature complete nor completed, so the products aren't necessarily 2008 compliant now.

  • Glad to hear it (about the bigger tests). Of course, different setups of IO, CPU, concurrent work load etc are issue too, but you're putting a down a good base to guide the rest of us in our own benchmarks.

    I'm going to assume that All Is Scriptable. I expect this simply adds a WITH COMPRESSION = XXXX to the SQL and/or a property to the SMO.

    Scriptability and the "SCRIPT THIS" button (for when you can't seem to get the 47 settings of a restore correct) is only thing that's saved my sanity (and my wrist) - being able to write my backup scripts in Perl/DBI and run them out of a Unix cron job.

    I don't know how anyone handles, say, 140 replicated databases plus development copies across 26 instances across 4 slabs of metal clicking guis and logs.

    Roger L Reid

  • Egad - where are my manners! I forget for a moment that redgate sponsors this excellent forum, and here I'm talking about SQL Server backup from Unix with Perl.

    I don't think they have to worry about a stampede in that direction. It's just my background showing. As usual, there's More Than One Way to Do It, and I know redgate does it for a lot of people.

    Roger L Reid

  • I took backup with both compress backup and do not compress backup. I was able to restore database with backup (do not compress backup) with out any issue. But, the one I took with compress backup option, I am not able to restore it. Is there any other parameter need to be given for this restore.

    Error I get is invalid backupset. Though I took backup thrice and all failed. Had any one got this issue?

    -- one more thing, I am using backup and restore with wizards and not commands.

    SQL DBA.

  • I will test this for you - when back at the office and give you feedback



  • I am very interested to see the restore metrics. What OS are you based on ?

  • I'd be interested in knowing if there is a difference in SQL 2008's compression compared to compression from SQL Litespeed.

Viewing 15 posts - 16 through 30 (of 54 total)

You must be logged in to reply to this topic. Login to reply