Skip to main content

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

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch