Restore a Backup of a TDE Database to Another Server: Level 2 of the Stairway to TDE

  • Comments posted to this topic are about the item Restore a Backup of a TDE Database to Another Server: Level 2 of the Stairway to TDE

  • Good one Vishnu. Made it look so simple and easy.

    Ramakant Dadhichi
    Technology Enthusiast

  • just one note to add here, if you do restore the DMK from another server, and i recommend you don't, you will need to take extra steps in order for the restores to occur seamlessly.

    When restoring the master database DMK across servers, the key is not silently protected by the SMK on the new sql server instance.

    If you restore the DMK from one instance to another and then restore your certificate everything will seem just fine. It's not until you attempt to restore the database that you'll get a failure, as the DMK cannot be silently opened, allowing access to the server certificate.

    To resolve this you will need to perform the following:

    --Open the DMK 
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah'
    --now set encryption by the SMK
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    As already stated though, you should ensure your DMKs are unique across instances.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply