How to set up TDE, back up certificates, and restore all to a new server

/**

-- This lets us look at existing encryption bits.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases


- This defines a master key

USE MASTER;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO

select * from sys.certificates

-- Now create the certificate

CREATE CERTIFICATE [SQL-A]
WITH SUBJECT = 'SQL-A CERTIFICATE'

select * from sys.certificates

-- Need to be in the user database

use mydb
go

-- Create an encryption key within the user database
-- I wonder if AES_128 is appropriate

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [SQL-A];
GO

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- This turns on encryption for that database

alter database mydb
set encryption on 
go

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

select * from students;

-- Backups are not that important.
-- Restores, however, are critical.

use master;
go

-- So let us back up the certificate

BACKUP CERTIFICATE [SQL-A]
TO FILE = 'C:\BACKUP\SQL-A.CER'
WITH PRIVATE KEY 
(
FILE = 'C:\BACKUP\MYDB.PVK',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
);
GO

-- And back up the actual database

BACKUP DATABASE [mydb] 
TO  DISK = N'C:\backup\mydb.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


-- We can restore that database to the original server 
-- without messing with certificates, keys, and passwords.
-- This would work if we were restoring on the same server as we started on.
-- That's because the certificate and keys are already installed/configured.
-- The restored database would already be encrypted.

USE [master]
RESTORE DATABASE [mydb2] 
FROM  DISK = N'C:\backup\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2_log.ldf',  
  NOUNLOAD,  STATS = 5

GO

-- The remaining commands would be run on a NEW destination server.
-- This server would not have an existing master key 
-- nor would it have the original certificates.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- So we need to set up a bunch of stuff.

USE [master]

-- Notice we have a brand new server with a brand new master key password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewPa$$w0rd';
GO

-- This is the critical key component.  
-- Here we re-create the original certificate
-- from the original certificate, private key, and password.
-- Note I had some trouble with SQL telling me that the certificate did not exist or that I did not have permissions to the file
-- It turned out there was an NTFS permission issue on the file.
-- When I copied the files to a new directory instead of attempting to restore from the original directory, 
-- all worked well.
-- So there was an NTFS permission issue, but I do not know exactly what.

CREATE CERTIFICATE [SQL-A]
FROM FILE = 'c:\RESTORE\SQL-A.CER'
WITH PRIVATE KEY (FILE = 'c:\RESTORE\MYDB.PVK',
DECRYPTION BY PASSWORD = 'Pa$$w0rd')
GO

-- And now we can restore the actual database.

RESTORE DATABASE [mydb] 
FROM  DISK = N'C:\RESTORE\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb_log.ldf',  
  NOUNLOAD,  STATS = 5

GO






*/