Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

One of my clients want us to give a safe estimate of how much disk space does a compressed SQL backup file take. They need to size their server hard disks accordingly.

We can rouhgly estimate the SQL database backup size (based on estimated number of records and etc), but what about when it is compressed?

I know this is hard to answer, but just from your experience, what would be a typical compression ration of an SQL Backup file?

share|improve this question

closed as not a real question by Jeff Ferland, EEAA, Khaled, Dave M, mdpc Mar 5 '13 at 15:51

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Run a backup, compress it, and find out for yourself. There is simply no way anyone other than you can give a sane answer to this question. –  EEAA Mar 5 '13 at 0:39
    
You don't normally back up indexes either and if you have more than a few of them their total size can exceed that data. –  Ladadadada Mar 5 '13 at 7:26
add comment

2 Answers

up vote 7 down vote accepted

It's impossible to answer this question without having even a small representative sample of the data in the database. If you're DB is full of mpeg videos then even using PAQ isn't going to compress the data more than a few percent.

If your DB is full of the same byte repeated then it's going to compress extremely small, maybe better than 99.9%. You're data is probable somewhere inbetween, so it'll compress somewhere between 0.1% and 99.9%.

The backup industry likes to advertise that "most" data can be 50% compressed. I've found this to be optimistic, but not terribly far off. I've found our MSSQL DBs generally compress less than 30% however.

share|improve this answer
2  
Also if you have grown your data files to be substantially larger than their data sets then you can get (seemingly) awesome compression ratios, that decrease as your free space is used. –  Mark Henderson Mar 5 '13 at 4:43
add comment

This is tough to answer but I just did a dump today on one of my databases (running PostgreSQL 9.2:

select pg_size_pretty(pg_database_size('mydb'));       
pg_size_pretty 
----------------
5660 MB
(1 row)

And after the dump:

% ls -lh

-rw-r--r--  1 pgsql  pgsql   363M Mar  4 16:42 mydb-20130304.sql.gz

So that's a 93.5% decrease in size.

share|improve this answer
3  
That's incredibly useless.... –  Chris S Mar 5 '13 at 2:29
add comment

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