Restoring a Database with Symmetric Encryption

Encryption was introduced into SQL Server with SQL Server 2005. This series of articles will tell you how to implement Encryption to protect your valuable data.

This blog is to how to restore a database to another server.

1. Backup the database on the [SOURCE] server.

2. Backup the service master key on the [SOURCE] server.

BACKUP SERVICE MASTER KEY TO FILE = 'C:\ENCRYPT_KEYS\MYSERVICE.KEY'
     ENCRYPTION BY PASSWORD = 'pa$$w0rd'

3. Restore the service master key on the [DESTINATION] server.

RESTORE SERVICE MASTER KEY FROM FILE ='C:\ENCRYPT_KEYS\MYSERVICE.KEY'

DECRYPTION BY PASSWORD = 'pa$$w0rd'    [FORCE]

4. Restore the database on the [DESTINATION] server.