Moving a TDE-protected database to another SQL Server

Follow these steps when you need to restore or move a TDE-protected database to another server.

Before you begin

Download this compressed file to the SQL Server machine: RestoreDBCertificate.zip. (If file doesn't download, right-click the link and save the file to your hard drive.)

Procedure

  1. Copy the two certificate files (AppScanEntCert.bak and AppScanEntCert.pvk) that you created in the Enabling Transparent Data Encryption on SQL Server databases task to a location on your machine (for example, C:\Certificate\).
  2. Open the SQL Management Studio of your SQL Server 2014 installation.
  3. Go to the location where you downloaded the RestoreTDECertificate.zip file. Unzip the file and open the script. (File > Open > File). You will notice several commands that will be executed on the server.
  4. Before you execute the script, you must set three fields for your environment (they are all marked with 'ACTION REQUIRED' in the comments section of the script):
    • DECLARE @MKPassword: The Master Key Password used to create the master key in the [master] database where you enabled TDE
    • DECLARE @BackupPassword: The password that was used to back up the certificate if it is different from @MKPassword
    • DECLARE @Path: The path of the location that you copied the two files AppScanEntCert.bak and AppScanEntCert.pvk
  5. After the fields have been updated, click Query > Execute to launch the script.

Results

After the script has completed, the result will be displayed in the 'Messages' window of SQL Management Studio. If you see the message: "The certificate is restored successfully, you can restore the database.", you should be able to restore the database on this SQL Server.