Ways to reduce MS SQL database backup file sizes

Question

This question might make one smile, really, HDD space is as cheap as dirt today. But nevertheless, with extremely slow internet connections (which is still the case in the major part of the world) transferring backups from different branches might be real pain in the back.

So, do you have any ideas on how to decrease database file size to a bare minimum? Absolutely any ideas are welcome.

Answer 1

If you're only talking about the backups, there's a few SQL Backup solutions out there that offer backup compression. SQL Server 2008 also has backup compression built-in.

See: Red Gate's SQL Backup Pro

or here is a list of various products: http://www.mssqltips.com/products.asp?catid=15

If you're talking about actually reducing the size of the database per se - pick the right (= minimal) data types, use maintenance plans to compress and reclaim space from the database, don't over-index (that always uses lots of space) etc.

Marc

Answer 2

Have you tried compression? Database files tend to compress up really well since they are usually mostly text.

Answer 3

1. Zip the backup.
2. Divide into multiple filegroups so you can backup files separately.
3. Drop indexes before backup. You might want to copy the DB locally before doing this.
4. Have indexes on a separate filegroup so you don't have to back them up.
5. Put text/binary/image data on a separate filegroup.
6. Do incremental backups.
7. Ask this question on http://www.serverfault.com