Compressing and Encrypting Your Backups

In the last article in this series, I discussed the commonly used options for Backup options and stressed that off-site backup were critical for disaster recovery. However, I also pointed out that the size of your backups combined with how many past backups you need to retain can be cost prohibitive. In this article, I’ll provide a high level overview of compression, security, and encryption of backups in SQL Server. I’ll cover SQL Server 2000 through 2008, since I know that a very large percentage of us are still using SQL 2000.

What is available natively in SQL Server?

If you are working with SQL Server 2000, your options are limited. SQL 2000 offers neither database nor backup encryption natively. Why does this matter? SQL backups can be read by an ordinary text editor. It’s not as easy as reading a Tom Clancy novel or anything, but it’s not that hard either. While it is true that you can password protect your backups, this is of little value. To demonstrate, I created a practice company (M2Mdata33) with the M2M sample database. I backed up the database with the following script. Notice that I’ve applied the password ‘bleh’ to it.


I opened the file in Notepad and found the following:

I can hear you saying, “Umm… David, you said it was human readable.” Actually all of the data in the file is readable. For example, when I try to search for any of the customer names in the educational database such as “Johnson Electric”, the following pops up:

Not exactly secure, huh? While it is true that if you attempt to restore the database without the password, you will receive an error; it’s not really secure. Unfortunately things didn’t improve much for SQL Server 2005. Database encryption was added, but it is applied on a column by column basis and from what I’ve been told, it’s a pain in the rear to administer. Backup encryption and compression were not addressed.

In SQL Server 2008, Transparent Data Encryption (TDE) was added which allows automatic encryption of your entire database. As the name would suggest, the encryption is transparent to both the users and the application, so in theory M2M would have no idea you encrypted its database. According to the demonstrations I’ve seen, the performance impact is negligible as well. I have not tested this and I only mention this option because I’ve received a couple of questions about it, but it doesn’t really pertain to M2M companies. TDE is an Enterprise Edition only feature, and I’ve never known a M2M customer to use SQL Enterprise as it’s prohibitively expensive. Backup compression is included in 2008 as well, but again only in the Enterprise Edition.

What about 3rd party products? What is the right way to handle backup compression and encryption?

There are two ways to compress and protect backups. There are several 3rd party options for SQL Server backups, and two of the most widely used are RedGate SQL Backup and Quest LiteSpeed. To be fair, I’ve not used either in production and have only experimented with these products on test machines. They both offer the following basic benefits:

  1. Increased Speed for backup and restore.
  2. Backup encryption is performed on the fly so the backups cannot be read without authorization.
  3. Backups are compressed around 90% according to the preliminary testing I performed on M2M Databases.
  4. Specific database structures such as tables can be restored rather than the entire database.
  5. Backup and restore process is simplified. More on this in a future article.

The only drawback is the cost of these products. Expect to pay anywhere from around $300 to $1500+ per SQL server for them.

What is the second way to handle backup compression and encryption?

What can you do if your boss won’t pony up and you need compressed and/or encrypted backups? Well, you could use 7 Zip, a free program. In testing, it compressed my backup file more than 90% and allows AES-256 Encryption and password protection. It compressed a 4gb database to 276MB, but took 20 minutes doing so. However, it is important to note that testing was performed on a retired PC with IDE drives. This product can be run from command line so it can be scheduled with the proper parameters and run every night after your backups complete. I’m setting up a system using this so long term backups can be stored on site rather than on off-site tape.

Other encryption options.

Most online backup companies support encryption, but this doesn’t protect the backup files that remain on your server on site. Also, some tape backup systems offer automatic encryption as well, but have the same drawback.

That’s great, but you still haven’t told me how to backup a database yet.

In the next article, we’ll discuss the Database Recovery Models for SQL Server and how to choose which is right for you.

What about you? Are you concerned with your backup security or the space required to store backups?

1 comment to Compressing and Encrypting Your Backups

  • Forrest Williams

    None of my clients use encryption on their backups.
    All rely on SQL server nightly backup.
    Most burn to DVD and store offsite.
    One sends offsite via ftp (automated nightly).
    A few do a test restore quarterly, but some not ever.
    I guess it depends on whether or not they’ve ever lost any data.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>