March 31, 2010 at 2:53 pm
matt stockham (3/31/2010)
fyi, compression is allowed in 2008R2 Standard Edition.
Awesome, but unfortunately we don't have access to 2008 R2.
Definately good to know this.
March 31, 2010 at 3:41 pm
FWIW, I haven't seen any reports of issues with backup compression to date.
I thought I had seen some tests that it wasn't as good as third party products, but it's probably "good enough" if you have Enterprise Edition.
March 31, 2010 at 3:55 pm
matt stockham (3/31/2010)
fyi, compression is allowed in 2008R2 Standard Edition.
which perhaps partly accounts for why it costs 25% more 🙂
---------------------------------------------------------------------
March 31, 2010 at 3:59 pm
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
But I also think it would be nice if there was an encryption option when doing backup/restore operations as well. I also think that native compression should be a part of SQL Server 2008 SE, not just EE (DE).Not sure I agree - EE is for large databases, large databases need compression. They have to draw the line somewhere.
Of course it would be cool if SE did everything EE does 🙂
there is no difference in maximum possible database size with standard and enterprise editions so people run some quite large dbs with standard. (my largest is is 300Gb)
Anyway, whats a large database, backup compression is pretty useful from quite small sizes (50GB?) up.
---------------------------------------------------------------------
March 31, 2010 at 10:13 pm
Just a note with native compression, make sure that you are running SP1 or greater. There was a bug that would cause the backup process to sit in a hung state in the event of insufficient disk space to complete the backup. The only way to clear this was to actually restart the instance.
Due to this issue I was running Hyperbac for quite some time, however have started cutting over to native since the bug was fixed.
March 31, 2010 at 10:26 pm
Nicholas Cain (3/31/2010)
Just a note with native compression, make sure that you are running SP1 or greater. There was a bug that would cause the backup process to sit in a hung state in the event of insufficient disk space to complete the backup. The only way to clear this was to actually restart the instance.Due to this issue I was running Hyperbac for quite some time, however have started cutting over to native since the bug was fixed.
Luckily we didn't start the transition until after SP1 wa released, so all 2008 instances are running SP1.
April 1, 2010 at 3:40 am
george sibbald (3/31/2010)
There is no difference in maximum possible database size with standard and enterprise editions so people run some quite large dbs with standard. (my largest is is 300Gb)Anyway, whats a large database, backup compression is pretty useful from quite small sizes (50GB?) up.
You know very well what I meant 🙂
I can quite see why compression would be deemed an Enterprise feature - though it is good to see it available on R2 standard.
April 2, 2010 at 9:17 am
Thanks for the input (this turned into a neat discussion!).
I just switched everything over to use backup compression for my full and differential backups, and it seems to be chugging along pretty good.
I haven't played with using it for transaction log backups, but it does a pretty decent job on the full and diff. backups. I don't quite get the same amount of compression as I was getting with PKZIPC, but it sure is a lot faster.
The Redneck DBA
April 2, 2010 at 9:30 am
Jason Shadonix (4/2/2010)
Thanks for the input (this turned into a neat discussion!).
Agreed. Good questions often spawn this sort of interesting exchange of views. Once of the reasons I like coming here. 🙂
April 2, 2010 at 9:35 am
Jason Shadonix (4/2/2010)
Thanks for the input (this turned into a neat discussion!).I just switched everything over to use backup compression for my full and differential backups, and it seems to be chugging along pretty good.
I haven't played with using it for transaction log backups, but it does a pretty decent job on the full and diff. backups. I don't quite get the same amount of compression as I was getting with PKZIPC, but it sure is a lot faster.
You may want to use compression on your t-logs as well. I am doing it on all three to great success.
April 2, 2010 at 9:36 am
Yea, you never know when you are going to pick up a useful nugget of information in the least likely place in the threads.
Personally, I think it's worth joining sqlservercentral.com just for privilege of reading the "Are the posted questions getting worse" and "DBCC TIMEWARP" threads!
The Redneck DBA
April 2, 2010 at 9:46 am
Lynn Pettis (4/2/2010)
You may want to use compression on your t-logs as well. I am doing it on all three to great success.
Yea, I thought about that. But I'm only keeping the full and diff. backups, and relying on database mirroring if we ever have a disaster for up to the minute recovery. (We have a job that periodically truncates the tx. log file for us).
Probably not totally koshir, but we've had a couple of "spactacular failures" to recover from in the past couple of years, and it's worked pretty well for us so far. (And it's worth noting that losing an hour of data from this particular server wouldn't be that big of a deal for us).
The Redneck DBA
April 7, 2010 at 8:34 am
(Note: have not read through every page of this post...so apologies if this may have been mentioned or asked)
I am fixing to start using compression on backups of SharePoint content databases. I wanted to do a backup of one of the databases to compare the compression.
This is the process I took:
--to enable compression
sp_configure 'backup comp', 1
go
reconfigure
go
--then tried a compressed backup, normal backup file size is about 15GB right now
backup database wss_db to disk = '<path>' with copy_only, compression
--which if you view http://technet.microsoft.com/en-us/library/ms186865.aspx,
--this looks acceptable
--However it does not compress the backup, it actually created a larger backup
--file than what was created this morning. Which could be some data was added,
--I'll buy that. But still did not see a compression occur
--decided to change around the code and did it as
backup database wss_db to disk = '<path>' with compression, copy_only
--saw compression occur this time
--original file size was 14.5GB from this morning.
--backup with compression created file that was 8.71GB.
Just curious but I don't recall reading anything within the TechNet article that mentioned a hierarchy requirement when doing WITH command. Why compression must come first. I'm fine with it, just funny that it is not mentioned...or am I the exception:w00t:
Running SQL Server 2008 Enterprise SP1 (10.0.2531.0)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 7, 2010 at 9:04 am
this worked on my SS2K8 Dev edition
BACKUP DATABASE db1 TO DISK = 'C:\db1.bak' WITH copy_only, COMPRESSION
April 7, 2010 at 9:09 am
Steve Jones - Editor (4/7/2010)
this worked on my SS2K8 Dev editionBACKUP DATABASE db1 TO DISK = 'C:\db1.bak' WITH copy_only, COMPRESSION
I don't have COMPRESSION first in my list of options either and it works.
But that does bring up an interesting point...I don't know that there is a way to tell if a backup is compressed or not, other than comparing the backup size to that of a non-compressed backup and noticing the obvious size difference.
On the other hand, since it doesn't make a difference when it comes time to do a restore on a database, I suppose it doesn't really matter that much.
The Redneck DBA
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply