Click here to monitor SSC

Focus on SQL Server

Are Your Backups Really Safe?

Published 16 September 2010 1:00 am

Imagine for a moment if you will. As a DBA, and as the protector of your organization’s data, you have implemented many safeguards to protect your data. You have set up periodic jobs to back up your databases; you check daily to ensure that the backups were actually taken; and you periodically perform test restores to ensure your backups work. In addition, you have established an appropriate backup retention policy and you store backups offsite. With all of this planning and hard work, you are confident that your organization’s data is safe. But is it?

What would happen if your backups, in transit from the data center to the offsite storage, were misplaced or stolen? Or what would happen if the backups at the offsite storage location were stolen? Sound unlikely? Not really. There have been many reported instances of backups being misplaced and stolen. In fact, I personally know of a multi-billion dollar company that sends their backup tapes to their offsite location using an employee’s car, then stores them in a locked, but very cheap metal cabinet that could be broken into in seconds with a crowbar. What would be the organization’s liability if their data was stolen? I’m even afraid to think about it.

Although it is unlikely that your backups will be misplaced or stolen, I personally don’t want to take the risk that this might happen to the backups I am responsible for. As you probably know, by default, data from a SQL Server database backup can easily be restored to any other computer and the data viewed. The exception to this is if the data has been encrypted, which is the exception rather than the rule.

In SQL Server 2008, Transparent Data Encryption (TDE) was introduced in the Enterprise Edition of SQL Server. This feature allows data at rest (such as backups) to be fully encrypted and protected should a backup be misplaced or stolen. If you are using SQL Server 2008 Enterprise Edition, I recommend you take a look at TDE and consider implementing it.

But what if your company doesn’t use the Enterprise Edition of SQL Server 2008, how do you protect your backups from prying eyes? Currently, your only realistic choice is to use a third-party backup compression program that offers data encryption as a feature (which virtually all do). This way, your backups, wherever they are located, will be protected and you can rest assured that your organization’s data won’t be compromised.

So my first question to you is this, do you think backup encryption is as important as I think it is? And second, how do you go about protecting your backups? Do you encrypt them, do you use some other form of backup protection, or are you crossing your fingers, hoping that you never face the situation of missing or stolen backups?

8 Responses to “Are Your Backups Really Safe?”

  1. syd_oracle says:

    The added concern is separating the decryption key from the backup. If you do encrypt your backup through some generic encryption tool (such as TrueCrypt) you want your key available so that you can restore that backup if needed, but backing up the key and the encrypted file to the same location could make the encryption redundant.

  2. says:

    TDE in SQL Server 2008 Enterprise is an excellent feature and I am very glad microsoft implemented it. With that said, it is important that people remember it comes with a price to be paid in terms of performance and additional maintenance that should cause any DBA to carefully evaluate it before implementing.

    I do however recommend encrypting the backups whenever practical. I personally like Red Gate’s SQL Backup with its built in encyrption. It is quite user friendly. but as syd_oracle mentioned, doing that you need to ensure that the encryption key used is carefully archived somewhere secure and separate from the database files themselves.

  3. Phil Factor says:

    A while back, I had to do an audit of security for a government database held in SQL Server. Without going into details, it held information which would have been extremely valuable to anyone unscrupulous who wanted to make a great deal of money.
    The DBAs proudly showed me some deeply impressive network security, some ingenious intrusion-detection systems, and other defenses against the data being viewed illicitly. They’d spent a lot of money.
    I was able to access the entire database within three hours, mainly (but not entirely) due to an unencrypted scheduled backup being left briefly exposed. The only way around this is to have a backup system that encrypts ‘on the fly’, in memory.

  4. Hugo Kornelis says:

    One of my major disappointments with Microsoft was the decision to make TDE an enterprise only feature. I think the decision makers in Redmont have been very short-sighted when they made that choice.

    Sure – there will of course be companies where management is already in doubt between standard and enterprise edition, and where the added TDE feature will nudge them over the edge towards the more expensive enterprise license. But there are many more companies where no other enterprise features would be used, and TDE alone will not be enough to justify the price difference. Those companies will not encrypt their backups, or encrypt with a thrid-party tool (exposing themselves to a short but realistic risc, as Phil comments).

    I think that making TDE available in standard edition, and maybe even in all editions up to and including the free express edition, would be a good decision. In the short term, Microsoft would lose a bit because customers choose standard edition over enterprise edition. But one of the major selling points of SQL Server over its major competitor has always been that Microsoft throws in all the good stuff for free, whereas Oracle charges extra for every option beyond the basic DBMS engine. This selling point is now completely invalidated, because one of the most important options for a database, security, in only included in the most expensive license.

  5. gbrayut says:

    I work for a small business and have to administer about 100 small databases. When we were looking at offsite storage our hosting provider wanted to charge upwards of $500 a months, which was way outside of our price range. I decided to instead write up a few Powershell scripts that would zip and encrypt the backup files using AES-256 encryption in 7Zip and then send them to Amazon S3 (“Designed to provide 99.999999999% durability and 99.99% availability”). Total cost: about $5 per month for nightly full and hourly differential backups on around 2.5GB of raw data (compressed to 205MB).

    I set the scripts to run as an hourly SQL Agent Job, so they get picked up and monitored by our Server Monitoring system, and am currently working on a method of automatically downloading them to a reporting server weekly to verify their integrity. All in all it works very well, but might not scale if you have tens or hundreds of GB to backup. If anyone else is interested I posted the scripts on my blog:

  6. bradmcgehee says:

    Gbrayut, I agree with you that compressing and encypting backups, and then storing them with an online backup company, such as Amazon S3, is a great solution for many companies with smaller databases. In fact, I use Amazon S3 to store all of my backups. Thanks for sharing your scripts.

  7. ColinM says:

    Something to bear in mind: TDE and native backup compression don’t mix well.

    “If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.”

    Third party backup tools don’t suffer from this issue.

  8. cjlotz says:


    Bit off topic here – but how do I go about “claiming my prize” for my comments on the previous month’s editorial? I tried contacting the editor at “editor at simple-talk dot com” but I have received no reply yet.


Leave a Reply

Blog archive