TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. When moving a TDE protected database, you must also move the certificate or asymmetric key that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files. For more information, see Understanding Transparent Data Encryption (TDE).

Create a backup of the server certificate in the master database. If the certificate that is used to protect the database encryption key is lost, you will not be able to access the data in a TDE protected database. The following example stores the backup of the certificate and the private key file, in the default data location for this instance of SQL Server (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA).

Security Note

You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key, does not have to be the same as the database master key password.

To performed this step by using SQL Server Management Studio, in Object Explorer, right-click the database, point to tasks, and then click Manage Database Encryption. Use the Manage Database Encryption dialog box to select a database encryption key and set database encryption on.

Create a simple table. Enter data into the table and then select from the table to test the database.

To move a TDE protected database to a new instance of SQL Server

To perform this step by using Management Studio, in Object Explorer, right-click the database, point to tasks, and then click Detach. Click OK.

Using Window Explorer, move or copy the database files from the source server to the same location on the destination server.

Note

Normally the two database files (CustomerRecords.mdf and CustomerRecords_log.LDF) will be in the default location C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. Your file names and locations might be different.

Using Windows Explorer, move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server. In our example, these files are stored in the default data folder at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.

Create a database master key on the destination instance of SQL Server. The password does not have to be the same as the source server.