InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:27:04 GMT20http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI like this feature very much.when i was using sql 2005, that time i was thinking about such feature. Finally Microsoft listen about it and provide it in sql 2008.i found only two drawbacks.1. Using more CPU.2, While restoring database, it'll take more time other then normal backup.But i m ok with all these... finally it's saving my drive's valuable space...Tue, 31 May 2011 00:19:52 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxIt is not 20-30% for all.my data base has 50 GB size and after compression in sql2008 , the backup file has 32GB.and another DB has 20 GB - backup file has 12 GB....so about 60% for some databasesTue, 28 Sep 2010 07:53:43 GMTashkan sirooshttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI noticed that during SQL2008 backup with compression, the backup file size is doubled until the backup is completed. For example, a database sized 10 GB with the final backup file 2 GB, the actual size (space used) during the backup is 4 GB. This means we need double the space for the backup operation.Does anyone experience the same? What is the story behind?Fri, 13 Mar 2009 20:27:27 GMTVivien Xinghttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHi MaryIt seems as if your version number is later than mine. If it is the developer version then it should work no problem.If you change the setting and have reconfigured then on the properties of the server should display the server settingThanksKevinFri, 16 May 2008 00:34:07 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxIf I change the backup compression in sp_configure to 1 (0 is default), will it use compression on all backups?Thanks,MaryThu, 15 May 2008 11:23:12 GMTSQLTIMEhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxWhat download of SQL2008 are you using for your backup example? My Feb CTP is 10.0.1300 - will your example work with the Feb. CTP?Thanks,MaryThu, 15 May 2008 11:17:40 GMTSQLTIMEhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxIs anyone using Symantec Backup Exec™ Continuous Protection, Server Continuous Protection for Microsoft® SQL Server Databases to replace their SQL Server backups and if so how does it compare to Redgate or Litespeed?We expect to be using the Continuous Protection Server, but initially just to protect our SQL Server backup files. We have not heard much about anyone using the product to replace SQL Server backups, and possibly log backups, too. But it does sound interesting.Mon, 12 May 2008 12:55:55 GMTJStineyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI've only used the Red Gate SQL Backup - it's compression is very good and you can vary the level of compresison, but I haven't had the chanec to compare to SQL 2008 compression yetCameronSun, 11 May 2008 18:16:09 GMTcameron.gibbshttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxAnybody done any third party compression vs. sql2008 compression?Sun, 11 May 2008 16:17:47 GMTGareth-313301http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHiThe install file is called: SQL2008_DEV_CTP3_Nov_ENU.exe (20 Nov 2007)SQL 2008 Developer Edition CTP3 November 2007Version Number = 10.0.1075ThanksKevinThu, 08 May 2008 23:40:51 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxKevin,Please state which edition(s) of SQL 2008 include the feature you are discussing in the series.Thanks.Thu, 08 May 2008 14:03:59 GMTJStineyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxThe document does say subject to change; and it doesn't say when it was compiled ( which is a bit annoying).I will send an email to my microsoft contact to see he can confirm anything has changed.Be a real shame, but I wonder if it is on purpose to keep the likes of red-gate products available.. does look purely marketing rather than that only enterprises would need to compress backups.Will post a reply if I get one from MS contact.Reply is : "Both backup compression and data compression are enterprise edition features. Restoring a compressed backup can be done using any edition. Usually the idea is that if it’s a scalability or availability related feature then its EE."It must have been the restore bit i remembered.Thu, 08 May 2008 01:51:11 GMTMichael Whiteleyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxSorry Michael - refer to this document published by Microsoft which clearly states that backup compression is Enterprise only[url=http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf][/url]THat's why we're looking at RedGate SQL Backup as an alternativeCameronWed, 07 May 2008 18:24:28 GMTcameron.gibbshttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI also agree that it should available in 2-3 versions. :)Wed, 07 May 2008 05:02:57 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHiThat does sound about right. If the SQL Engine has the compression built in then it would be crazy to have diffirent versions or limitations based on the edition.But yes having data compression on the fly when storing data to the datafile is still seen as a new exciting feature and would more than likely be a selling feature for Enterprise edition.But the good news is that this technology now exists and it should eventually become a standard feature for all editions - we just hope that this does not take too lon to get to us.Wed, 07 May 2008 02:17:04 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxbackup compression is available in all editions.Data compression of the online database is an enterprise only feature, which is different from backup compression.That is what I was told at a microsoft seminar.Wed, 07 May 2008 02:04:43 GMTMichael Whiteleyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxOf course SQL backup compression is great....but my understanding is that it is only available with SQL Enterprise edition - which costs a helluva lot more than Standard.Why oh why Microsoft woudl you make something like backup compression an Enterprise only feature....I can understand the BI and analysis stuff being pared down...but not THISTue, 06 May 2008 18:26:18 GMTcameron.gibbshttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxExcellent article and and a very nice analysis of a new feature in 2008. Of course, the feature is much less impressive to people like me already using something like SQL Backup 5 which has excellent compression, but its nice to have the option available. I would also like to join the call for similar metrics and analysis of restore. It would also be interesting to see a comparison between this and what SQL Backup 5 does in terms of speed and compression ratios.Tue, 06 May 2008 11:24:31 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHaving worked with the Redgate folks for over three years now, I would say they are more interested in advancing technology and making good products for their customers, reahter than being worried about the platform.They have been hands down, one of the 5 best vendors I have ever worked with. But one would expect no less from a bunch of Guiness swilling British engineering types!! :DTue, 06 May 2008 09:06:20 GMTRick Sheeleyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxBe interesting to see how this compression compares to software products that already do compression, is the sql 2008 better, worse or the same ? Tue, 06 May 2008 07:35:12 GMTMichael Whiteleyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspx[quote][b]Nik Southworth (5/6/2008)[/b][hr]My Test Results.SQL 2008 w/ CompressionSize: 3.39GBTime: 12 MinsCPU%: AVG 40%SQL 2008 wo/ CompressionSize: 14.0GBTime: 21 MinsCPU%: AVG 30%Restore details to follow.[/quote]Good findings. In terms of speed and size the difference is almost more than 50%. The more big the database the more difference will come I believe. But one thing what will be the compression rate of image field?Tue, 06 May 2008 06:00:01 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxMy Test Results.SQL 2008 w/ CompressionSize: 3.39GBTime: 12 MinsCPU%: AVG 40%SQL 2008 wo/ CompressionSize: 14.0GBTime: 21 MinsCPU%: AVG 30%Restore details to follow.Tue, 06 May 2008 03:46:43 GMTNik Southworthhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI do not have any info on a better spec'd server - but may in the near future.I have not investigated the possibility of adjusting the compression percentage, but this would be an interesting idea.The only things that you may save in a less compression setting is speed and CPU, but this may be required if you consider the limitations on disk speeds and CPU utilization etcThanksKeviMon, 05 May 2008 23:34:36 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHiThe environment was a Virtual PC - running XP SP2 with 1GB RAM.There was no other applications running on this machine at the time of the backup. ThanksKevinMon, 05 May 2008 23:28:59 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxKevin,Nice work man... going by the screenshots it seems like you had this running on UniProcessor Environment and with 1 GB RAM.couple of questions - Any chance you have metrics on a better server? and secondly, was there anything else running on this box when you took either of the backups?I am concerned about the spike in CPU load during the compressed backups. Seems to put quite a lot of load the box. Wondering if there are any settings to limit the compression algorithm to minimal/medium/full... etc..Mon, 05 May 2008 16:49:36 GMTABHILASH DHONGDIhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI'd be interested in knowing if there is a difference in SQL 2008's compression compared to compression from SQL Litespeed.Mon, 05 May 2008 14:28:40 GMTjefwilsonhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI am very interested to see the restore metrics. What OS are you based on ? Mon, 05 May 2008 13:40:23 GMTJohn Chiuhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI will test this for you - when back at the office and give you feedbackThanksKevinMon, 05 May 2008 11:22:19 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI 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.Mon, 05 May 2008 11:12:47 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxEgad - 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.Mon, 05 May 2008 10:53:34 GMTRoger L Reidhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxGlad 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. Mon, 05 May 2008 10:49:51 GMTRoger L Reidhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxThis 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.Mon, 05 May 2008 10:36:54 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxIt 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, BannerAMCMon, 05 May 2008 10:30:55 GMTRick Sheeleyhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxHiI 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 findingsThanksKevinMon, 05 May 2008 10:06:02 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxNicely 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.ThanksMon, 05 May 2008 09:51:34 GMTRoger L Reidhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxYes, 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.ThanksKevinMon, 05 May 2008 09:42:26 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxVery interesting numbers. Since I have to ship backup files across a low-speed network, this could come in very useful.Mon, 05 May 2008 09:26:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxI think this is an Enterprise feature onlyMon, 05 May 2008 08:17:35 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxSQL 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)ThanksKevinMon, 05 May 2008 08:15:16 GMTKevin-458339http://www.sqlservercentral.com/Forums/Topic494665-1267-1.aspxGreat post! Question: Is this feature available with Std Edition or only Enterprise?Mon, 05 May 2008 08:02:14 GMTSequelDBA