question

ramesh 1 avatar image
ramesh 1 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
sql-server-2008backup-compression
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

loving the "extensive research"!!! +10 (if I could but you only actually got +1)
1 Like 1 ·
Thanks, @Fatherjack! Of course, I restrained myself from posting the link I wanted to post...
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
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.)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

One thing they did differently was remove the ability for you to change the level of compression...
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Ramesh - please use comments rather than answers when responding to others' answers/comments.
1 Like 1 ·
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.
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It depends, Phil. Backup compression is available in the Standard Edition of R2.
3 Likes 3 ·
Oops, forgot that.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.