CODEWITHSUNDEEP
sql-serverbackupfilesizeminimize
Pavel Bastov
Pavel Bastov

Reputation: 6971

Ways to reduce MS SQL database backup file sizes

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.

Upvotes: 3

Views: 4840

Answers (3)

Jonathan Parker
Jonathan Parker

Reputation: 6795

  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

Upvotes: 6

1800 INFORMATION
1800 INFORMATION

Reputation: 135463

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

Upvotes: 0

marc_s
marc_s

Reputation: 755531

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

Upvotes: 0

Related Questions