SQL Server 2008 Backup Encryption

Posted: May 18, 2009 in Uncategorized

For the last couple weeks and for the next couple of weeks I am going to be heads down working on a couple of workshops. These workshops are requiring me to do a lot more research so I can make a deep dive into a product feature. The work shop that I have been working on this past week or so has been all about Backing up and restoring your database. It sounds easy enough, but we really wanted to go deep on these subjects, we wanted to talk about not just the basics but way that you can restore without restoring, really the things that you should know and love being a SQL Server DBA.

Well thought the process of putting this workshop to an outline format I had made a note about backup encryption, I was going to cover it at the same time I talked about backup compression. However I needed a demo on the subject I needed to make sure that I was showing using the code that I was about to present on. As I was working on the demo I was looking for information on the syntax. The search got a little more frantic when I could not find anything, I searched the backup syntax, but there was nothing about encryption. I searched the encryption subjects but was unable to find anything about backups.

Before I went any further I contacted someone over at Microsoft and asked them if I was just missing it somewhere. I was sure I missed it I could have sworn that SQL Server 2008 support backup encryption. Well the final outcome, it does not, well not by itself. So why do I highlight this? I think it’s important to understand the ways that you can support security on your backups. Here is the way that I can figure.

  • Back up to an encrypted disk partition or share
  • Back up with a password (weak ā€“ easily broken)
  • Turn on Transparent Data Encryption (TDE) ā€“ all files, including tempdb and backup files are encrypted.
  • Third-party software ā€“ There is backup compression software out there that you can add to your SQL Server that will encrypt your backup.

I wanted to thank my friend over at Microsoft for the assistance, and the above bullet points. Have a great week.

Comments
  1. Brent Ozar says:

    You’ve got a typo in the third-to-last sentence that says “I was sure I missed it I could have sworn that SQL Server 2008 support backup compression.” You meant encryption, I think. 2008 supports backup compression, but not encryption. Right on about everything though.

  2. ram says:

    Yes i was hoping to see that in R2 atleast.Hopefully someone put that request in connect site requesting feature to be added in next version atleast.

  3. Pankaj says:

    Thnx for sharing this information….

  4. Rajesh says:

    Chris, thanks for the info. When you say backups are not encrypted, I hope you are taking a holistic view/considering everything for that perticular DB. Is it fair to say “ONLY DATA” is encrypted in SQL backups ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s