March 11, 2009 at 5:44 pm
Guys,
What is the best way to disable TDE. I uncheck the "Set Database Encryption On property" but I am still not able to restore or attach the database on a different server.
The value of is_encrypted in sys.databases is 0 (after the uncheck).
The error message while restoring the database on a different server
Msg 3283, Level 16, State 1, Line 1
The file "AdventureWorks_Log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
and thumb print error while doing attach.
Amit Lohia
March 12, 2009 at 5:42 am
This seems to work. Set it to off, backup the database with a new backup and you should be able to restore to another server without the need for certificates.
ALTER DATABASE dbname
SET ENCRYPTION OFF
"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 12, 2009 at 10:44 am
Same issue. Even restart of instances do not work. Did anyone try this?
Amit Lohia
March 12, 2009 at 11:03 am
Yep. Just did. Works great. Turn on encryption on one machine, including setting a certificate and enabling it on the database. I ran a backup from there that was encrypted. I then disabled encryption, ran a backup and restored that backup to another server. All done. No issues.
"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 12, 2009 at 11:11 am
How large is the db? might take time to undo encryption.
March 12, 2009 at 3:13 pm
Can someone run the following commands and confirm if this is working for them.
You will need two server or instances and I am assuming server already have Master Key
USE master;
GO
-- Please make sure to create a newcertificate because your existing certificate may be on both
--server.
CREATE CERTIFICATE NewCertificate WITH SUBJECT = 'To Test TDE'
go
CREATE DATABASE TDEDisabling
GO
USE TDEDisabling
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE NewCertificate
GO
ALTER DATABASE TDEDisabling
SET ENCRYPTION ON
GO
-- We have Encription ON
-- Verify Encription
WAITFOR DELAY '00:00:30'
SELECT * FROM sys.dm_database_encryption_keys
GO
BACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Trying restoring the backup on a DIFFERENT server/instance it will fail (due to encryption)
-- Delete the backup file
ALTER DATABASE TDEDisabling
SET ENCRYPTION OFF
GO
WAITFOR DELAY '00:00:30'
SELECT * FROM sys.dm_database_encryption_keys
--Confirm no encryption is going on
GO
BACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Restoring on a different server/INSTANCE.
RESTORE DATABASE [TDEDisabling] FROM DISK = N'J:\Backups\TDEDisabling.bak' WITH FILE = 1, MOVE N'TDEDisabling_log' TO N'I:\Logs\TDEDisabling_1.LDF', NOUNLOAD, STATS = 10
GO
-- I am still getting the following error. Though I see the database but it is not accesable
14 percent processed.
24 percent processed.
34 percent processed.
44 percent processed.
54 percent processed.
64 percent processed.
74 percent processed.
84 percent processed.
94 percent processed.
100 percent processed.
Processed 160 pages for database 'TDEDisabling', file 'TDEDisabling' on file 1.
Processed 1 pages for database 'TDEDisabling', file 'TDEDisabling_log' on file 1.
Msg 3283, Level 16, State 1, Line 1
The file "TDEDisabling_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Amit Lohia
March 12, 2009 at 3:27 pm
Steve Jones - Editor (3/12/2009)
How large is the db? might take time to undo encryption.
I confirmed the status and it is not even working on a new database.
Amit Lohia
March 12, 2009 at 5:27 pm
I think that's a torn page error. Try running a consistency check on the source db.
"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 12, 2009 at 5:41 pm
There's definitely something wrong. Can you backup and restore the source without issues to itself?
March 12, 2009 at 5:43 pm
No TORN page error either. Same issue with new database also. Did you try using the command I post.
Amit Lohia
March 13, 2009 at 7:59 am
I hit the same issue. I went back to retest and now I'm hitting the issue on the test database, where I didn't hit it before...
I'm trying to track down if anyone else knows about this. I can't find anything so far. I think we're going to have to open an issue on Connect. I just did a few searches there. There doesn't seem to be anything. This is either a bug, or we're missing something. I'm just confused by how I got it to work before.
"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 8:14 am
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.
"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 8:20 am
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.
"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 8:27 am
Nope removing the certificate didn't fix the issue. That makes me feel better. There must be an alter database statement that removes the reference to encryption.
"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 9:07 am
Or it's removal is coming in SQL 11 :w00t::hehe:
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply