DBA - How To Disable TDE on a Database and Restore To Another Server

Scenario:

Once you enable the TDE ( Transparent Data Encryption ) on a Database, Even you Set it Off and take the backup you will not be able to restore to another server without creating Certificate. In our Scenario we need to provide database without TDE to one of our vendors for testing. We don't want to provide them the Certificate and Key.

Solution:

Here are the steps to disable TDE on SQL server database and then take backup and restore to another server without creating Certificate.

Step 1: 
Get the Certificate Name with respect to Database

USE MASTER
GO
SELECT CR.name                 AS CertificateName,
       
DB_NAME(EK.database_idAS DBName
FROM   sys.dm_database_encryption_keys Ek
       
INNER JOIN sys.certificates CR
               
ON EK.encryptor_thumbprint CR.thumbprint

Step 2: 
Set the encryption off on Database by using below script.
USE MASTER;
ALTER DATABASE [YourDbName]
SET ENCRYPTION OFF;
GO

Step 3:
Drop Database Encryption Key by using below script 
USE [YourDbName]
DROP DATABASE ENCRYPTION KEY

Step 4: 
Drop Certificate on Database by using below script. Get the certificate name by using script in Step 1.
USE MASTER
DROP 
CERTIFICATE YourCertificateNameThatYouWantToDrop

Step 5: 
Take the backup of Database and then Restore to destination where you want to. It would let you restore without any error.