I read from microsoft doc about TDE:
Back up the master key and certificate that are used for TDE to a safe location. The master key and certificate are required to restore backups that were taken when the database was encrypted with TDE. After you remove the database encryption key, take a log backup followed by a fresh full backup of the decrypted database.
For the bolded text, I understand the need for do a fresh full backup, but why we need to do a log backup before that?
Thanks,
March 30, 2021 at 7:45 pm
Do you really care that much? That is, isn't it easier just to take the log backup.
I think I know the reason: it would be the only way to get the db back to the exact image it was before you turned off TDE. That is, if you have a total db failure after turning off TDE, and you need to back to the TDE-enabled version of the data, the only way would be to use the tlog backup created at that time.
The odds of needing it are probably very low, but unless the log file is huge, why not just back it up and not worry about all the details?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 30, 2021 at 7:57 pm
Thanks, Of course I can just back the log up, and do the full backup.
My question for this post is try to figure out what is the reason to do that.
Also I have a habit that usually if I understand better the logic of the procedure, it is easy for me to remember it to do it.
Thanks any way.
I did give what I think is the reason for the recommendation: to be able to get back to the exact db image is something happens after removing TDE.
I get what're you saying about the reasons and understanding the logic. My thinking was that this is so obscure and unusual an action -- removing TDE from a db -- that it might not be worth the time to figure this particular one out. But I understand being curious. Many of us get so pressed for time we, sadly in a way, let those things go.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2021 at 9:42 am
because the log backup actually resets marks the log to be overwritten.
Preceding a full backup, you are sure that the next log backup will no longer contain encrypted log data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply