SQL Server 2008 Backup Encryption

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.