Remove Certificate

Note:

• Need to remove all current database using certificate before you can drop the certificate.
• Once the certificate is removed, all database restore of encrypted database will fail.

USE master
go

ALTER DATABASE TestEncryptedDB SET ENCRYPTION OFF

USE TestEncryptedDB
GO
DROP DATABASE ENCRYPTION KEY   

USE master
GO

DROP CERTIFICATE Mycertificate01
DROP MASTER KEY

Restore Database without Certificate

-- Restore without Certificate
RESTORE DATABASE TestEncryptedDBWithoutCertificate
  FROM DISK = N'D:\SQLServerBackups\TestEncryptedDB\TestEncrypedDB_Full.bak'
  WITH MOVE 'TestEncryptedDB' TO N'D:\MSSQL\TestEncryptedDB02\TestEncryptedDBWithoutCertificate.mdf',
       MOVE 'TestEncryptedDB_log' TO N'D:\MSSQL\TestEncryptedDB02\TestEncryptedDBWithoutCertificate_log.ldf';
GO

Msg 33111, Level 16, State 3, Line 60
Cannot find server certificate with thumbprint ‘0xCC4472D721418B759E36A04034F0C2A95EE68DD6’.
Msg 3013, Level 16, State 1, Line 60
RESTORE DATABASE is terminating abnormally.