Sql-server – Backup and restore SQL Server database with encrypted columns: what should I backup along with database

always-encryptedsql server

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:

Create database master key;
Create certificate;
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:

Master key;
Master key password;
Certificate;
Certificate private key;
Symmetric key.

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

Best Answer

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

Related Solutions

Sql-server – Restore encrypted database to another server

I wrote a blog entry about mirroring and TDE.

It took me a while to figure out that to get the db on the secondary server, I needed the code line:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
 GO

The password is the same one that I used to encrypt the master key file. After issuing the command on the 2nd SQL server, everything played nicely. Here is the db.stackexchange post I made also.

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Best Answer

Related Solutions

Sql-server – Restore encrypted database to another server

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

Related Question