March 13, 2009 at 10:46 am
Grant Fritchey (3/13/2009)
Nuts. I might have been premature in posting to Connect. I finally found another reference:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0
I'm testing removing the certificate now.
Yep, I check that too. It was only post I find related to this issues.
Amit Lohia
March 13, 2009 at 10:55 am
Grant Fritchey (3/13/2009)
I posted it to Connect:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249
You can vote on it, set the priority, add your comments, especially since this is your issue.
I'm going to keep hacking at it as time allows.
I do not think it will make much difference but my testing is on platform 64
Amit Lohia
March 13, 2009 at 11:00 am
Amit Lohia (3/13/2009)
I do not think it will make much difference but my testing is on platform 64
I think that's worth noting. Now we know it happens on two platforms. If there's a way to fix it, we should find out, I hope. I also blogged about[/url] it and sent out a tweet. Hopefully someone with an answer will notice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2009 at 1:01 pm
If we analyze the error from backup command. It is complaining about log file. So I thought how about trying to attach just MDF file but it failed.
sp_attach_single_file_db @dbname = '[TDEDisabling]', @physname = 'J:\Backups\TDEDisabling.mdf'
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xBB1B0816079C10A6342683443A8E576196EFBE73'.
Msg 1813, Level 16, State 2, Line 1
Could not open new database '[TDEDisabling]'. CREATE DATABASE is aborted.
Amit Lohia
December 7, 2010 at 12:20 pm
I know this thread is over a year old but I'll post in case someone else runs into this problem.
I've run into the need to know how to remove TDE. In doing my research I came across this thread and some other links with similar issues. The blogs say that setting encryption to off isn't enough. You also need to drop the database encryption key. Only after performing these two steps can you create a fresh backup of the database and restore it to a new server without encryption.
USE MASTER
GO
ALTER DATABASE DBNAME
SET ENCRYPTION OFF
GO
USE DBNAME
GO
DROP DATABASE ENCRYPTION KEY
GO
reference this link:
July 6, 2011 at 7:24 am
Can you make sure you backup database to a different .bak file after you turn off the encryption?
January 9, 2013 at 10:58 pm
Did you use this query to turn off the TDE.
USE MASTER
GO
ALTER DATABASE DBNAME
SET ENCRYPTION OFF
GO
USE DBNAME
GO
DROP DATABASE ENCRYPTION KEY
GO
June 27, 2013 at 11:52 am
nice one....thats right code
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
March 4, 2016 at 9:56 am
Hello,
u need to create master key and restore the certificate with the private key on the new server before starting restore the database . After that open the master key and run the restore command. It worked for me. waiting encryption state to change to 3 is not gone work. It will take forever.
use master
open master key encryption by password ='xxxxxxxx'
restore database xyz from disk=N'c:\xyz.bak'
go
March 6, 2016 at 6:21 am
you do not need to restore the DMK or even open it to restore a TDE protected database to another server!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2018 at 7:58 am
Follow two step before you make a backup to move to new server.
Step 1:
ALTER DATABASE <User DB> SET ENCRYPTION OFF
Step 2:
USE <User DB>
GO
DROP DATABASE ENCRYPTION KEY
GO
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply