How to compress your database… REALLY!

NB: Do NOT try any idea in this post at home, and DO NOT try it on your production server!

Here are some thoughts on database backup strategies, which were brought upon after reading Brent Ozar’s post on the topic.

In short, the problem is that Brent has a database which has to be backed up and the backup file needs to be as small as possible in order to save money from the file transfer to / from the colo facility. (They charge by the byte. )

So, the proposed solution is:

1. Restore the production backups
2. Do some work on them to decrease their size
3. Back up the smaller databases

The “Do some work” part is:

1. Script out all of the non-clustered indexes in the database
2. Save those definitions to a table (or a stored procedure)
3. Create a stored proc that will loop through those index definitions and recreate them later
4. Drop the indexes

Also, Brent suggests to rebuild all tables with 100% fill factor.

…

Now. Sounds great, but only if you have non-clustered indexes and fill factors less than 100. And if you don’t? Here are some thoughts on this:

1. How about starting with a flawless database design? Following the design best practices can really give you good performance and save you quite a few bytes: use proper data types and proper normalization.

2. How about some backup strategy which includes differential backups, partition backups, and so on? You don’t really need to backup archived (and rarely or never changing) data too often.

3. How about using sparse columns, if possible.

And after this, it all “flies off the handle”:

4. How about exporting all data to raw or flat files, compressing it with zip, rar or something, then backing up the database and transferring it?

5. How about finding a cheaper colo center? Some that does not charge by the byte?

6. How about not backing up anything? Who needs backups, after all.

Note: with this blog post I do NOT mean to challenge anyone (especially Brent, whom I respect endlessly as a SQL Server professional!), and I hope I am not offending anyone. The reason I am writing this note is the fact that I have seen so many poor database designs, so many misused datatypes and so many databases that have never been backed up, that when I read Brent’s post, I thought “Ha, it must take a data transfer paid by the byte to make people think how to optimize the space used by their data!”

And finally, here is a really good post, which came also as an answer to Brent’s blog: Chad Miller explains how to use PowerShell to back up all non-clustered indexes in a database.