Backup and restore SQL Server database with encrypted columns: what should I backup along with database?

Question

Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use encrypted columns I had to:

1. Create database master key;
2. Create certificate;
3. Create symmetric key.

Which of those are stored along with backup, and which I should backup manually and restore on the other server manually as well? I see there is SQL statements backup master key, restore master key, backup certificate, create certificate ... from file. So, along with database itself, there are:

1. Master key;
2. Master key password;
3. Certificate;
4. Certificate private key;
5. Symmetric key.

So what (and how) should I deal with when I restore my database on a fresh server? Thank you!

• M.Ali: Have you used TDE to encrypt data in the source system?
• nyan-cat: @M.Ali no, I use column encryption with EncryptByKey / DecryptByKey functions.

Answer 1

You are using column encryption. This is not always-encrypted. When you backup your data base and restore it, you only need to recover the master key. Anything else will be restored through the backup file.

You can restore the master key with this command:

USE [Your_DB_Name]
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Your_MasterKey_Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

Answer 2

To restore a TDE enabled backups on a different server, you need to do the following:

1. Backup Certificate along with the databases on the source system
2. Ensure there is already a master key on the destination server if not create one
3. Create certificate on the target server from the Backup files (Certificate backup file and the private key file) from the source server
4. Finally, restore backup and it should work.