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