For those unfamiliar with encryption in SQL Server, this article is a nice introduction to symmetric encryption in SQL Server 2005. I’ve only used encryption a few times and it’s definitely been the godsend that the article states.
I referenced the article recently when I had to restore a database on a new server that had data that was encrypted on the existing server. I was actually surprised in the end how easy this task was to do.
1. Backup the database on the [SOURCE] server.
2. Backup the database master key on the [SOURCE] server.
BACKUP MASTER KEY TO FILE = ‘C:MASTER.KEY’
ENCRYPTION BY PASSWORD = ‘password’
3. Backup the service master key on the [SOURCE] server.
BACKUP SERVICE MASTER KEY TO FILE = ‘C:SERVICE.KEY’
ENCRYPTION BY PASSWORD = ‘password’
4. Restore the database master key on the [DESTINATION] server.
RESTORE MASTER KEY FROM FILE = ‘C:MASTER.KEY’
DECRYPTION BY PASSWORD = ‘password’
ENCRYPTION BY PASSWORD = ‘password’
[ FORCE ]
5. Restore the service master key on the [DESTINATION] server.
RESTORE SERVICE MASTER KEY FROM FILE = ‘C:SERVICE.KEY’
DECRYPTION BY PASSWORD = ‘password’
[FORCE]
6. Restore the database on the [DESTINATION] server.
And viola… I was now able to decrypt the data. These steps, of course, only work if there isn’t a service master key already on the new server. And some day I’ll likely run into that situation. But I’ll blog that then…
EDIT: My bad, restoring the database master key is bad. Fortunately the time I had to do it there was only a single database on the test server so there was no negative impact at the time. Unfortunately, I blogged badly following that.
Strate SQL 