Assymetric Encryption – Remove TDE

ALTER DATABASE DatabaseName
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a
value of  1 in the query below. */

SELECT encryption_state, d.name, percent_complete
FROM sys.dm_database_encryption_keys e
INNER JOIN
sys.databases d ON
d.database_id = e.database_id;
GO
USE DatabaseName;
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE master
go
DROP CERTIFICATE TDE_Certificate
go
DROP MASTER KEY
go

Note: IF All of tde is removed from the server, TEMPDB will still show up as encrypted. This is by design to make sure any residual data from an encrypted database within the TempDB is still encrypted and secured – even if the actual database no longer resides on that server. Tempdb will be free from any encryption on a restart of sql server.