DataGinger.com

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE

Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

USE [master]
RESTORE DATABASE [TDE_Test_restore] FROM  
DISK = N'C:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Output:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

USE master; 
GO 
BACKUP CERTIFICATE TDECert
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

Create a Master Key in destination server.

The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'

After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

CREATE CERTIFICATE TDECert2
FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Password12#')

Restore Database in destination server

We will now be able to restore the encrypted database backup successfully.

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5

Listed are some of the advantages and disadvantages of using TDE.

Advantages

• The performance impact of TDE is minor. Estimated to be around 3-5%
• Performs real-time I/O encryption and decryption of the data and log files
• Encrypts the Entire Database in rest
• No architectural changes needed
• No application code changes are required and the user experience is the same
• Easy to implement
• DBAs can still see the data

Disadvantages

• No protection for data in memory
• Not granular – Cannot just encrypt specific tables/columns
• Not good for high CPU bottleneck servers
• Not protected through communication/networks