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.
Reason for this issue:
(Microsoft’s statement at https://connect.microsoft.com/SQLServer/feedback/details/351548/rc0-engine-disabling-tde-from-a-database-does-not-allow-it-to-be-attached-restored-later):
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.
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.
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.
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