Backup and restore of TDE Databases to different server

Question

There seems to be a lot of information on backing up and restoring TDE databases, but it's generally all the same information, and there seems to be some contradiction about certain points. I have some specific questions I hope someone can answer.

1. Do I have to backup the Database Master Key and restore it? Or is it enough to recreate it with the same password?
2. Similarly to #1, do I need to backup and restore the Service Master Key?
3. If I've backed up the TDE encryption certificate and cert key, can I restore it to a different database that has a different DMK (and SMK) without losing data?
4. If #3 is yes, then what is the purpose of backing up the DMK and SMK if you can just generate new ones when restoring the Cert?

Answer 1

You need to refer to architecture of TDE encryption

Do I have to backup the Database Master Key and restore it? Or is it enough to recreate it with the same password?

Yes and NO. Always backup your DMK !

Yes

The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

Yes - when you have to restore the database during a DR scenario (rebuild the server).

No - when you dont have the cert Private key password and want to restore the cert to different server, you can simply backup the cert with a new password and restore on the different server.

Remember that - A DMK does not encrypt the database. It is used to encrypt the certs and other keys to keep them protected.

Similarly to #1, do I need to backup and restore the Service Master Key?

A rare scenario, but its good to have it backed up as part of best practice. A SMK (##MS_ServiceMasterKey##) is the root of encryption and is generated automatically the first time it is needed to encrypt another key. If SMK is corrupted, then you might need to restore it.

For 3 and 4 ..

You can, but why would you do it ?

You have to turn off encryption, create a new cert with new Private key and then turn on encryption. Depending on your database size, this can take some time.

Remember that a DEK is the one that does the encryption and decryption of the database.

As a side note, refer to this script for info on the databases, encryption status along with other useful information.

Answer 2

The key to understanding how to manage moving a TDE encrypted database from one server to another is understanding that the private key of the certificate is the only key needed to decrypt the Database Encryption Key in the TDE enabled database. The encryption heirarchy below the certificate can contain different values for the keys, as long as it is fully functional. When the TDE Certificate and private key is restored, the server will encrypted it using the DMK in the master database. If a DMK does not exist and you create a new one, then it will protect the certificate private key with the new DMK. When you execute a command to create a DMK, the server encrypts it using the password you provide and also encrypts it using the SMK, so you don't need to create it with the same password because the server can decrypt it when needed using the SMK. As long as each keys in the heirarchy can decrypt the subsequent key, the TDE will work.

The encryption heirarchy in SQL Server is implemented based on the ansi x.917 standard. While backing up and restoring a DMK may not apply to TDE, it works well when restoring a database with column level encryption. If there were no command for backing up the DMK and SMK, then restoring a database with column encryption would require restoring the master database and implementing the same service account on the destination server to regain control of encrypted column data, which is practically rebuilding the server to restore one database.