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.

  • Article

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

Reviewed by

Nickson Dicson & Shamik Ghosh

Technical Lead, Microsoft SQL Server.