Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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
Why was this closed as not real question? Even when it can't be reasonably answered, I don't find it ambiguous, vague, incomplete, overly broad, nor rhetorical, and it's not difficult to tell what is being asked – Mario Trucco Jun 30 at 14:17
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
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

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'));       
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
That's incredibly useless.... – Chris S Mar 5 '13 at 2:29
FWIW, I ran that same experiment and got a 92.7% reduction: select pg_total_relation_size('mytable') returns 5659951104 (5.7GB). A dump file of the same table (format=custom, default compression level) is size 413748266 (414MB). The table in question has many repeated text strings and numeric values, making it eminently compressible. – user207863 Feb 19 at 15:34

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