Restoring the TDE Encrypted DB to Another Server

If a restore attempt of a TDE encrypted backup file is attempted, a failure such as the one shown in Figure 17.12 will occur. To be able to restore the backup file, the target server will need to have the TDE Certificate restored to it.

Figure 17.12. Viewing an error generated when attempting to restore a TDE-encrypted database from backup.

The high-level steps for restoring a TDE-encrypted database to another server are as follows:

1. Create new DMK on target server. (Each DMK is unique, so simply create a new one using the TSQL listed in previous steps. This DMK does not need to match the one from the source server.)
2. Back up the certificate and private key from the source using the TSQL script shown previously.
3. Restore the TDE Certificate and the private key onto the target (no need to export the DEK, as it is part of the backup file). Use similar syntax to what is shown here:

   

4. Restore the database file from backup.

Note that the decryption password needs to match the one chosen in the previous steps.

Using this process, administrators can encrypt their critical SharePoint data without the need for complex third-party solutions.