Disabling TDE on SQL Server 2008

Or more accurately: Preparing an encrypted TDE enabled database for restore on a Standard Edition SQL Server

I recently had the challenge of restoring an encrypted database on to a standard edition server to enable further development on the database code. It took some work, but to save you some time, I’ve listed the steps (and the T-SQL) in this article that you need to take in order to accomplish this.

First you will need to disable TDE on the database concerned. (Make a copy of this database and work off that if you can’t disable encryption on the production database).

-- Disable encryption
ALTER DATABASE MyDatabaseName
SET ENCRYPTION OFF
GO

Once this is done (it may take a while to rescan the files) you need to drop the database key.

USE MyDatabaseName
DROP DATABASE ENCRYPTION KEY
GO

Remember that the transaction log will still be encrypted if it hasn’t been truncated. So to force this to occur (in SQL2008 there is no more BACKUP LOG WITH TRUNCATE_ONLY) switch to simple recovery mode.

ALTER DATABASE MyDatabaseName
SET RECOVERY SIMPLE
GO

This should issue an automatic checkpoint, but you can also issue one manually.

CHECKPOINT
GO

You can now backup and restore the database as you would normally.

Hope this helps

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.