asked
Backup compression in SQL Server 2008
sql server 2008 is having a advantage of database backup compress. which algorithm is used to compress the backup. question seems to funny , but i am planning to made some R&D on developing a custom backup
I would be careful before you jump into creating that. Due to internal structures you could mess things up fairly quickly. Read the warnings on disk defragmenting and SQL Server as a reference.
According to my [extensive research][1], it's a MS-proprietary backup algorithm. This gives me what some friends call "Teh Fear". I still get flashbacks to "doublespace" or "diskdoubler" or whatever it was called back in the early 90's... [1]:
http://www.google.co.uk/search?q=sql+server+2008+backup+compression+algorithm
loving the "extensive research"!!! +10 (if I could but you only actually got +1)
Thanks, @Fatherjack! Of course, I restrained myself from posting the link I wanted to post...
According to the SQL Server 2008 Internals book, it's a proprietary algorithm that is very similar to Zip. (Makes you wonder what they did differently.)
One thing they did differently was remove the ability for you to change the level of compression...
OK i agree with that ,but what generally happens in a zip algorithm is it just searches the file with a repeated word and it replaces it with a asci code. when unzip it will replace the letter with the repeated word example tody went to **fruit** market , in the **fruit** market he met his friend who was selling **fruit**s. he bought some **fruit**s also ate some **fruit**s there in the above paragraph we can see word fruit is repeated 5 times. now fruit is replaced with ^ symbol now you see the paragraph how it is tody went to ^ market, in the ^ market he met his friend who was selling ^ he bought some ^ also ate some ^ there ((((key is ^=fruit))) this is how zip is done. but in sql server 2008 database. we have very less probability of word repetition . then how does it compress the backup
@Ramesh - please use comments rather than answers when responding to others' answers/comments.
That's a huge over-simplification of how zip works. Basically, it's looking for bit patterns, not words, and bit patterns happen to be repeated very often in most structured data streams. The modifications they probably made to the zip format was having a different static huffman tree that contained matches for the most common bit patterns found in page headers / row headers.
Backup compression and table compression are done in completely different ways. Both are only available only in Enterprise Edition. Ramesh's explanation is nearest to table compression. There are third party tools that will do both of these more efficiently, and cheaply.
It depends, Phil. Backup compression is available in the Standard Edition of R2.
