locked
Database Backup/Recovery pre and post TDE RRS feed

  • Question

  • I've seen conflicting version of the procedure to encrypting a database.   Since the database is available during the encryption process.. Can I expect to be able to recover my database if it remains in FULL Recovery Mode during the encryption process and while the application is connected/executing?   Some version of the procedure indicate changing the Recovery Mode to SIMPLE during the encryption process.  I'm not sure what that buys me.  Ideas?   Thanks.
    Tuesday, March 27, 2018 7:06 PM

Answers

  • "I've read articles that call for a Backup after switching the recovery mode to SIMPLE just prior to encrypting the database....and I'm not sure why."

    Perhaps just to have a "safepoint" before encryption to restore to, in case you would need it? But that is guessing, without a motivation in those articles you've read we are left to guesses. :-)

    "Is there a downside to leaving the recovery mode = FULL during the encryption process? "

    SQL Server will modify all data in the database, so I guess all this will be logged to the transaction log. (I recommend a test, just to make sure.) Having the database in simple will allow SQL Server to empty the log during the encryption process.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by bdalepa Monday, April 2, 2018 5:27 PM
    Monday, April 2, 2018 10:04 AM

All replies

  • Not sure what the question is here...

    The encryption isn't done until you have also cycled the transaction log (emptied the log so you have no un-encrypted log records). Until then it is in-progress. This assures you that when encryption is complete, you have no un-encrypted data anywhere, and nor does a subsequent backup carry any un-encrypted data.

    As for whether the certificate need to be available for a restore, I can only assume that if you reverse the process (turn off encryption) then you have to wait until this is completely finished until a backup can be restored without the certificate. Obviously, having anything still encrypted "in there" means that the certificate need to be available for a restore.

    I have a feeling that you were actually wonder about something different from my free-floating thoughts above, so please elaborate.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, March 27, 2018 7:35 PM
  • Interestingly enough if you initiate the TDE encryption process and then restore from last nights backup, it will remove TDE from that database.

    So if you recover your database to a point in time before you enabled TDE on that database, it will not be protected by TDE.

    If you recover your database to a point in time after you enabled TDE on that database, it will be protected by TDE.

    Similarly if you enable TDE on your database and immediately restart your SQL Server, it will be protected by TDE and the process of encrypting your database totally will proceed.

    Tuesday, March 27, 2018 7:57 PM
  • Tibor..  Sorry for the lofty question.  Basically, I'm trying to finalize the procedure for encrypting an SQL Database.  What would be the sequence of events with regard to Recovery Mode and when backups should be taken.  I've read articles that call for a Backup after switching the recovery mode to SIMPLE just prior to encrypting the database....and I'm not sure why.   Is there a downside to leaving the recovery mode = FULL during the encryption process? 
    Wednesday, March 28, 2018 5:33 PM
  • "I've read articles that call for a Backup after switching the recovery mode to SIMPLE just prior to encrypting the database....and I'm not sure why."

    Perhaps just to have a "safepoint" before encryption to restore to, in case you would need it? But that is guessing, without a motivation in those articles you've read we are left to guesses. :-)

    "Is there a downside to leaving the recovery mode = FULL during the encryption process? "

    SQL Server will modify all data in the database, so I guess all this will be logged to the transaction log. (I recommend a test, just to make sure.) Having the database in simple will allow SQL Server to empty the log during the encryption process.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by bdalepa Monday, April 2, 2018 5:27 PM
    Monday, April 2, 2018 10:04 AM