In Level 1 of this Stairway, we discussed how to configure TDE in a user database using a Database Master Key and Certificate. In this level, we explain the steps for backing up and securing this certificate, as well as how to restore the database on another server instance.

Backup the Certificate and Private Key

We need to use the BACKUP CERTIFICATE statement to take the backup of the certificate. We got a message after creating the DEK in Level 1 that we needed a certificate backup. This is how we perform the backup.

BACKUP CERTIFICATE TDETest_Certificate
TO FILE = 'D:\tde\TDETest_Cert'
WITH PRIVATE KEY (file='D:\tde\TDETest_CertKey.pvk ', ENCRYPTION BY PASSWORD='StrongPasswordgoeshere')
GO

the arguments are:

• TO FILE = 'path_to_file'  -  complete path, including file name, of the file in which the certificate is to be saved.
• WITH PRIVATE KEY        -  specifies that the private key of the certificate is to be saved to a file.
• ENCRYPTION BY PASSWORD = 'encryption_password' -  password used to encrypt the private key before writing the key to the backup file.

This script will create a TDETest_Cert file and TDETest_CertKey.pvk private key in the location 'D:\tde\' in source server.

Fig 1: Certificate and private key backup location

Note:  The BACKUP CERTIFICATE command does not have the option to overwrite the existing file. Hence,TDETest_Cert file should not exist in the location specified while taking the certificate backup.

If we are restoring the database without the TDE certificate and private key, we will encounter the certificate missing error as the database is secured with the certificate.

Restore TDE enabled database in another instance

This section describes the steps to backup and restore the TDE enabled database backup to another instance. The steps involved include the backup of the database, the restore of the certificate backup to the destination server, and finally the restore of the database backup. Some of these steps only need to be done once, as subsequent restores will complete without issue, as long as the certificate remains the same.

Backup the Database

We start by issuing the BACKUP DATABASE statement to take a full backup of the TDE enabled database, TDETEST.

BACKUP DATABASE [TDETEST] TO  DISK = N'D:\tde\TDETest.bak'

Fig 2: Output - Database Backup

Restore this database backup in the new instance 

We can issue the RESTORE DATABASE statement to restore the database to another server instance. While performing the restore, you will encounter the below error if the certificate that we created in the first section is not imported. The reason is the certificate that was used to encrypt the database on the source server is not available on the destination server instance, and thus the data cannot be decrypted.

We can see this when we run this command. The results in Fig 3 show the restore cannot complete.

USE [master]
RESTORE DATABASE TDETest FROM  DISK = N'D:\tde\TDETest.bak'

Fig 3: Output - Restore error due to certificate missing

The below steps will show how to restore the TDE enabled database backup to the new server instance. The steps to be followed in the destination server are shown below.

• Create a Database Master Key (DMK)
• Certificate creation
• Restore TDE enabled database

Database Master Key Creation

The first step is to create a Database Master Key (DMK) in the master database on the destination server with a password. This password doesn't need to be the same that was used in the source server for DMK creation. If there is already a DMK present in the destination server, there is no need to create another one.

USE master; 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DifferentP@$$w0rd'; 
GO

Fig 4: DMK creation in destination server

Once this step has been completed, it doesn't need to be repeated for future restores.

Certificate creation

The certificate and the private key that we created from the source server with the BACKUP CERTIFICATE statement have to be copied to the destination server instance. Once the files have been copied, use the CREATE CERTIFICATE statement to add the certificate to the master database in the destination server. Note the FROM FILE and FILE parameters point to the certificate and private key files. The paths should match the location to which you copied these files.

USE MASTER
GO
CREATE CERTIFICATE TDETest_Certificate
FROM FILE = 'D:\tde\TDETest_Cert'
WITH PRIVATE KEY (FILE = 'D:\tde\TDETest_CertKey.pvk',
DECRYPTION BY PASSWORD = 'StrongPasswordgoeshere' );

FROM FILE  -  complete path, including file name, of the certificate. The location can be in the destination server or a location accessible to both source and destination servers.

WITH PRIVATE KEY        -  specifies that the private key of the certificate.

DECRYPTION BY PASSWORD = 'key_password' -  password used to decrypt the private key.

The below figure shows the expected result after execution.

Fig. 5: Importing the certificate

Once this step has been completed once, it does not need to be repeated for future restores.

Restore the TDE enabled database

Once the certificate and the private key get imported to the destination server, the restore process will be successful.

USE [master]
RESTORE DATABASE [TDETest] FROM DISK = N'd:\tde\TDETest.bak' WITH FILE = 1, 
MOVE N'TDETest' TO N'E:\SQL\MSSQL14.MSSQLSERVER\MSSQL\DATA\TDETest.mdf', 
MOVE N'TDETest_log' TO N'E:\SQL\MSSQL14.MSSQLSERVER\MSSQL\DATA\TDETest_log.ldf', NOUNLOAD,REPLACE, STATS = 5
GO

Fig 6: Output showing restore as successful

Summary

This stairway article explains on how to restore a TDE enabled database to another server instance. It depicts the steps to backup the certificate from the source server and restore it on the destination server for the successful database restore.

Also, it is important to use strong passwords during the certificate backup step and assure that the keys and passwords are managed to keep  them safe with proper backups and security.