Sql-server – Backup and restore of TDE Databases to different server

backuprestoresql server 2014transparent-data-encryption

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?

Best Answer

You need to refer to architecture of TDE encryption

enter image description here

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.

Related Question