Error initializing transaction logs on restore

8 03 2014

An issue created through the use of Transparent Data Encryption (TDE)

To sandbox a set of databases for a specific application a named instance was created on the SQL server. The Original instance had TDE encryption enabled and all databases were encrypted. After creating the new instance, the databases to be moved were decrypted.

ALTER DATABASE <DATABASE NAME>  SET ENCRYPTION OFF

A backup of each database was then taken to be restored on to the new instance. This practice was used over a detach / re-attach method as testing was needed against the new instance in preparation to migrate the application to the new instance.
Attempting to restore the databases I received the following errors in the SQL error log, the restored databases did not become functional due to this issue.

Error: 3283, Severity: 16, State: 1.
The file “XXXXX_log” failed to initialize correctly. Examine the error logs for more details.

After investigation it was reported that this was a known error that occurred on databases that were encrypted by TDE upon restoring them to a new server or instance. Microsoft recommends applying Cumulative Update 4 to resolve the issue. After applying the latest Cumulative Update from Microsoft, Update 11, the problem still persisted.
Further research indicated that although the databases had been decrypted they would require the original certificate they were encrypted with to be restored into the new instance or server. The solution is to implement TDE on the new instance. In implementing TDE on the new instance the standard procedure was followed except with the cvaveat of the certificate. A new one was not genereated, instead the originating database servers certificate was was imported during the configuration.
It is important to note that when TDE is used, backups of keys and certificates are necessary. Store those backups in multiple locations. Failure to take appropriate precautions will result in catastrophic data loss.

/* create a backup of the service master key */
BACKUP SERVICE MASTER KEY TO FILE = 'C:\temp\service_master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Create master key for the new instance */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO

/* Backup the new master key */
BACKUP MASTER KEY TO FILE = ' C:\temp\master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Here we import the certificate from the original server */

CREATE CERTIFICATE
FROM FILE = ' C:\temp\certificate.cer'

/* We need to use the original private key we used to encrypt the certificate */
WITH PRIVATE KEY (FILE = ' C:\temp\private_key.pvk', DECRYPTION BY PASSWORD = '');
GO

/* Backup the certificate for the new instance or server */
BACKUP CERTIFICATE TO FILE = ' C:\temp\certificate_YYYY-MM-DD.cer' WITH PRIVATE KEY (FILE = ' C:\temp\private_key_YYYY-MM-DD.pvk', ENCRYPTION BY PASSWORD = '')
GO

After configuring TDE and using the original certificate restores from the original database server to the new instance work without any issue.