It’s a very common scenario where customers have their database encrypted and hold multiple certificates on their SQL Server environment. In this blog I would be sharing some good information on steps to restore full backup that was taken at the time when the database was not encrypted and applying the Transaction logs backups which was taken across different certificates. Here is one of the example which will also help you to understand the steps to create, restore and backup certificates for better encryption management.
In this series I would be using two SQL Server instance to demonstrate steps.
Server1 : Holding Source database which initially is un-encrypted.
Server2 : Target server to restore the backup.
Below are the Steps:
Step1. On server1 take a full backup for the database "testdb"
backup database testdb to disk ='\\File_path\testdb.bak'
Step2: Create Database Master Key (if not created already):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passw0rd1'
Step3. Create Certificate "Cert1" on Server1
CREATE CERTIFICATE Cert1 WITH SUBJECT = 'SQL TDE CERT'
Step4. Backup certificate "Cert1".
BACKUP CERTIFICATE Cert1 TO FILE = '\\File_path\cert1.cer'
WITH PRIVATE KEY ( FILE = '\\File_path\private1.pvk' ,
ENCRYPTION BY PASSWORD = 'Passw0rd11');
Step5. Create DEK and encrypt with first certificate "Cert1":
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE Cert1
Step6. Enable encryption on testdb:
ALTER DATABASE testdb SET ENCRYPTION ON
Step7: Take a Transaction log backup on testdb
backup log testdb to disk ='\\File_path\testdb_log1.trn'
Step8. Create Certificate "Cert2" on Server1
CREATE CERTIFICATE Cert2 WITH SUBJECT = 'SQL TDE CERT2'
Step9. Backup certificate "Cert2":
BACKUP CERTIFICATE Cert2 TO FILE = '\\File_path\cert2.cer'
WITH PRIVATE KEY ( FILE = '\\File_path\private2.pvk' ,
ENCRYPTION BY PASSWORD = 'Passw0rd12');
Step10. Re-encrypt "testdb" with "cert2"
ALTER DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE Cert2
Step11. Take a Transaction log backup on testdb
BACKUP LOG TESTDB TO DISK ='\\File_path\testdb_log2.trn'
Now, we have a full backup (testdb.bak) of an un-encrypted database, a transaction log backup of the database encrypted with server certificate Cert1 and another transaction log backup of the database encrypted with server certificate Cert2.
Going over to server2(Target server) to restore, we aim to restore the full backup and both the transaction log backup on the server2
Note:- Before you follow below steps on Target Server, ensure that Server 2 have Master key already created on it, else you might get below error:
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
Step1. Restore "Cert1" on server2
CREATE CERTIFICATE Cert1
FROM FILE = '\\File_path\cert1.cer'
WITH PRIVATE KEY ( FILE = '\\ashutosh-2003\share\blogtest\private1.pvk' ,
DECRYPTION BY PASSWORD = 'Passw0rd11');
Step2. Restore "Cert2" on server2
CREATE CERTIFICATE Cert2
FROM FILE = '\\File_path\cert2.cer'
WITH PRIVATE KEY ( FILE = '\\ashutosh-2003\share\blogtest\private2.pvk' ,
DECRYPTION BY PASSWORD = 'Passw0rd12');
Step3. Restore Full backup in appropriate location with NORECOVERY on Server2:
RESTORE DATABASE testdb
FROM DISK = '\\File_path\testdb.bak'
MOVE 'testdb' TO
Step4. Restore log backup (testdb_log1.trn) with NORECOVERY:
RESTORE LOG testdb
FROM DISK ='\\File_path\testdb_log1.trn'
Step5. Restore log backup (testdb_log2.trn) with RECOVERY:
RESTORE LOG testdb
FROM DISK ='\\File_path\testdb_log2.trn'
The restore should work as expected. Please note that we can perform Step1 and 2 on Server2 after Step3 (as full backup restore was not encrypted).
Additionally I would also like to add that, it’s a best practice to make a copy of the Certificate and Private Key backed up at a safe place and note the Master Key Password too.
Regards, Ashutosh TripathiSE, Microsoft SQL Server CSS
Nickson Dicson & Shamik Ghosh
Technical Lead, Microsoft SQL Server.