Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

I have a database with full backup of almost 2GB. When I perform a full backup (without compression) and then compress it with winrar it become 140MB, but when I backup WITH COMPRESSION syntax the result is 315MB, and unfortunately after compression with winrar the size not changes. How can I use T-SQL for full compression at maximum level?

share|improve this question

migrated from stackoverflow.com Jan 18 at 12:30

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Paul White, Max Vernon, RolandoMySQLDBA, Marian, Kin Jan 22 at 14:27

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
For simplicity, and to ensure that compressed backups are always faster and minimize the additional CPU that is used, there is just one compression level. Microsoft knows that there are 3rd parties that have better compression ratios, and they are quite happy for you to use those solutions if the difference is that important to you. –  Aaron Bertrand Jan 18 at 19:49
1  
My experience is that I am getting better overall performance with native, compressed backup on a ~5TB database than with LiteSpeed 7. The file size is bigger than the LiteSpeed backup, but we're completing in roughly 1/3 of the time. I'd imagine this is due at least in part to the substantially higher context switching that naturally occurs with LiteSpeed. –  swasheck Jan 18 at 20:36
    
^^^^ That was a comment that may seem unrelated (because it is), but I just wanted to point out that there are many, many factors that need to be taken into consideration when you're evaluating products' relative benefits against each other. –  swasheck Jan 18 at 20:43
    
You might get some help from this previous question: The smallest backup possible … with SQL Server. –  Marian Jan 18 at 21:37

2 Answers 2

up vote 5 down vote accepted

SQL Server's built-in backup compression only has one level of compression (as Mark and Adrian have already said). The most likely reason that you are getting a smaller final file by compressing the uncompressed backup is that it will contain large amounts of plain text. WinRAR and other traditional compression utilities are very good at compressing plain text.

An alternative means of getting smaller backup files is to use a third-party backup application. There are several of these on the market, including Red Gate's SQL Backup Pro and Idera's SQL Safe Backup. These contain more efficient compression algorithms that should give you smaller backups without post processing. I don't use either of these personally so can't recommend a specific one.

share|improve this answer
2  
And keep in mind that if you back up a database using a 3rd party compression algorithm, you need that tool to restore the database, too. So it doesn't work well if you are moving backups between environments and don't have licenses everywhere, let your license lapse, etc. –  Aaron Bertrand Jan 18 at 19:47
2  
There was a tool that provided a compressed backup compatible with zip - Hyperbac. If you didn't have the tool on another server, you could use any other tool to uncompress and then you had the full backup available. But, unfortunately, Red Gate has killed it in the mean time. –  Marian Jan 18 at 20:38

As far as I know, there is only one compression level in SQL Server and subjective observation puts it at about 30-40% of uncompressed data volume.

Have seen SQL Server compressed .bak files being further compressed by 7-Zip

share|improve this answer
1  
Agree, there is only 1 level of compression in SQL Server. If you want the best compression possible you will need to do some type of automation with another script or application. But it is more management overhead –  Adrian Sullivan Jan 18 at 8:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.