How to restore database backup (Full and Transaction logs) after regenerating Database Encryption Key (KEY) with a new certificate having no impact on Log sequence number (LSN) chain.


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.


 


Prerequisite :


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”


 


USE MASTER


GO


backup database testdb to disk =‘\\File_path\testdb.bak’


GO


 


Step2: Create Database Master Key (if not created already):


 


USE MASTER


GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Passw0rd1’


GO


 


Step3. Create Certificate “Cert1” on Server1


 


USE MASTER


GO


CREATE CERTIFICATE Cert1 WITH SUBJECT = ‘SQL TDE CERT’


Go


 


 


Step4. Backup certificate “Cert1”.


 


USE MASTER


GO


BACKUP CERTIFICATE Cert1 TO FILE = ‘\\File_path\cert1.cer’


    WITH PRIVATE KEY ( FILE = ‘\\File_path\private1.pvk’ ,


    ENCRYPTION BY PASSWORD = ‘Passw0rd11’);


GO


 


Step5. Create DEK and encrypt with first certificate “Cert1”:


 


USE TESTDB


CREATE DATABASE ENCRYPTION KEY


WITH ALGORITHM = AES_128


ENCRYPTION BY SERVER CERTIFICATE Cert1


 


Step6. Enable encryption on testdb:


 


USE MASTER


GO


ALTER DATABASE testdb SET ENCRYPTION ON


GO


 


Step7: Take a Transaction log backup on testdb


 


backup log testdb to disk =‘\\File_path\testdb_log1.trn’


 


Step8. Create Certificate “Cert2” on Server1


 


USE MASTER


GO


CREATE CERTIFICATE Cert2 WITH SUBJECT = ‘SQL TDE CERT2’


GO


 


Step9.  Backup certificate “Cert2”:


 


USE MASTER


GO


BACKUP CERTIFICATE Cert2 TO FILE = ‘\\File_path\cert2.cer’


    WITH PRIVATE KEY ( FILE = ‘\\File_path\private2.pvk’ ,


    ENCRYPTION BY PASSWORD = ‘Passw0rd12’);


GO


 


Step10. Re-encrypt “testdb” with “cert2”


 


USE TESTDB


ALTER DATABASE ENCRYPTION KEY


REGENERATE


WITH ALGORITHM = AES_256


ENCRYPTION BY SERVER CERTIFICATE Cert2


GO


 


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


 


USE Master;


CREATE CERTIFICATE Cert1 


      FROM  FILE = ‘\\File_path\cert1.cer’


            WITH PRIVATE KEY ( FILE = ‘\\ashutosh-2003\share\blogtest\private1.pvk’ ,


                  DECRYPTION BY PASSWORD = ‘Passw0rd11’);


GO


 


Step2. Restore “Cert2” on server2


 


USE Master;


CREATE CERTIFICATE Cert2


      FROM FILE = ‘\\File_path\cert2.cer’


            WITH PRIVATE KEY ( FILE = ‘\\ashutosh-2003\share\blogtest\private2.pvk’ ,


                  DECRYPTION BY PASSWORD = ‘Passw0rd12’);


GO


 


Step3. Restore Full backup in appropriate location with NORECOVERY on Server2:


 


USE Master;


RESTORE DATABASE testdb


   FROM DISK = ‘\\File_path\testdb.bak’


   WITH NORECOVERY,


      MOVE ‘testdb’ TO


‘C:\testdb.mdf’,


      MOVE ‘testdb_log’


TO ‘C:\testdb_log.ldf’


 


Step4. Restore log backup (testdb_log1.trn) with NORECOVERY:


 


USE Master;


RESTORE LOG testdb


      FROM DISK =‘\\File_path\testdb_log1.trn’


            with NORECOVERY


GO


 


Step5. Restore log backup (testdb_log2.trn) with RECOVERY:


 


USE Master;


      RESTORE LOG testdb


            FROM DISK =‘\\File_path\testdb_log2.trn’


                  WITH RECOVERY


GO


 


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 Tripathi
SE, Microsoft SQL Server CSS


Reviewed by


Nickson Dicson & Shamik Ghosh


Technical Lead, Microsoft SQL Server.


 


 

Comments (0)