Msg 3283, Level 16, State 1, Line 1 – Restore fails for a database which was encrypted using TDE (transparent data encryption) but now encryption is disabled



When you disable encryption on a database which was using TDE (Transparent data encryption) and then take it’s backup and try to restore it on another instance (which does not have the certificate of original encrypted instance), you may experience below error:

Msg 3283, Level 16, State 1, Line 1
The file " " failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Although restore process completes, database never starts and essentialy backup file remains unusable.
When a key is added to the database it is required to start the database. Just turning encryption off does not remove the key and it should be removed manually after the need for it is removed. Which brings up the other point. The log is encrypted on a logical file granularity so the data is encrypted/decrypted by the scan, but the log must wait for the logical log to switch to the next file to change keys/encryption. There may still be a need for the key if the last checkpoint or open transaction at the last checkpoint goes back into the prior logical log files as they need to be read to perform recovery. The encryption status from the DMV will indicate if a scan is still in progress and what the current keys are - it will show in progress until the logical log file rolls over.
As a general rule ake sure to keep all keys/certs around as you will need them for any backup spanning when they were active.

Please follow below steps to resolve this situation:

Step 1:
Make sure that your SQL Servers are updated appropiately. If you are running with latest service pack as of today (SP1 for 2008R2 or SP3 for 2008) then you should not have to do anything on this step. Please refer to http://support.microsoft.com/kb/2300689 for details from Microsoft on this known issue.

Step 2:
Disable TDE completely on the database before you take the backup.

Execute below list of commands to completely disable TDE on the database in question:

A.

SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys
where DB_NAME(database_id) = 'Database Name’

You should receive encryption_state of the dabase as 3 which confirms database in encrypted.

B.
USE [master]
GO
ALTER DATABASE [Database Name] SET ENCRYPTION OFF
GO

C.
SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys
where DB_NAME(database_id) = 'Database Name’

Now you should receive encryption_state of the dabase as 1 which confirms decryption is complete. This process may take sometime for a bigger database based on your environment.


D.

Caution: Execute this step only when C is completed.

use
[Database Name]
drop database encryption key
go

E.
SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys
where DB_NAME(database_id) = 'Database Name’
Now this should not return any data as key is dropped succesfully. At this point database is completely restorable to any other comtable system.


Step 3: Follow your standard backup-restore procedure and you will not need any special measure to restore this database on any other compatible instance. However please be advised that any backup taken before you disabled TDE completely (Per Step 2 of this post) remains encrypted and can not be restored with out restoring original certificate from source server to destination one.


Note: When you received the original error, although the database never started (ie. Never came online), they were technically restoring. So you will see them in your database list with a status of “RECOVERY_PENDING” . You may want to drop such database to clean your instance.





Comments

Anonymous said…
Excellent post!
Anonymous said…
Excellent post