none
sql server TDE database refresh RRS feed

  • Question

  • Hi Gurus,

    I have a database server with below spec:

    SQL server 2016 Enterprise

    TDE enabled on a database DB1.

    Taken the backups of Certificate (cert1) and Private key with a strong password of this database DB1.

    Question:

    When I refresh the database DB1 with a fresh copy of database backup,I then follow steps to enable TDE with the certificate (cert1)

    Do I need to take the backup of the certificate with private key again as I restored the database and enable TDE  again?

    Kind Regards,

    Chinna

    Monday, October 12, 2020 11:14 AM

All replies

  • Yes , you need to back up the certificate that was used to encrypt the database. Execute the following T-SQL script to create the certificate backup and the private key file in the mentioned path. MyProduct_Cert is the name of the certificate. Replace the name of the certificate with yours. check more on this guide  https://www.sqlshack.com/restoring-transparent-data-encryption-tde-enabled-databases-on-a-different-server/
    Monday, October 12, 2020 12:17 PM
  • Hi Alex

    If you look at the below steps when we create TDE  for the first time 

    First time script

    --step 1
    USE MASTER
    GO

    CREATE CERTIFICATE [01_Certificate_LIVE]

    WITH SUBJECT ='01_Certificate_LIVE'
    GO

    --step 2

    USE [DB]

    GO

    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM =AES_256
    ENCRYPTION BY SERVER CERTIFICATE [01_Certificate_LIVE]

    --step 3

    USE MASTER
    GO
    ALTER DATABASE DB
    SET ENCRYPTION ON
    GO

    After few days I wanted to refresh the database form a backup I do the below steps only 

    USE [DB]
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM =AES_256
    ENCRYPTION BY SERVER CERTIFICATE [01_Certificate_LIVE]
    GO
    USE [master];
    GO
    ALTER DATABASE [DB] SET ENCRYPTION ON
    GO

    so why do I need to take the backup of the certificate again? as its the same

    Monday, October 12, 2020 3:16 PM
  • This forum handles requests related to Open Specifications documentation issues. 
    The Open Specifications can be found athttp://msdn.microsoft.com/en-us/library/cc203350.aspx. 
    Your question does not appear to be related to the Open Specifications documentation set. 
         
    I suggest you try Q&A to get assistance.

    https://docs.microsoft.com/en-us/answers/products/sql-server 

    Mike Bowen

    Escalation Engineer Microsoft Open Specifications

    Monday, October 12, 2020 4:29 PM