Month: May 2015

Restoring a Decrypted Database

Customer has TDE encrypted database that needs to be restored onto another SQL Server instance.  He turned off encryption, backed it up with a verify, and copied it to the other instance.

During the restore, it goes to 100% then gives the following error:

100 percent processed.
Processed 89426 pages for database 'MyDatabase', file 'mydatabase' on file 1.
Processed 4 pages for database 'MyDatabase', file 'mydatabase_log' on file 1.
Msg 3283, Level 16, State 1, Line 2
The file "mydatabase_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Microsoft says this was fixed in Service Pack 2 and the customer is on SP2.

Frustrated

Try this:

DROP CERTIFICATE Certificate_Name
go
DROP DATABASE ENCRYPTION KEY
go

Back up the database again.

Restore it on the other instance

*Remember to always keep a backup of the certificate used for database encryption, even after you have decrypted it.  You never know when you’re going to need to restore an old backup.

Advertisements