How to restore a database (TDE Enabled) from one SQL Server instance to another SQL Server instance

I assume that you are taking backup and restoring the database on the same version and edition of SQL Server Instances.

We will get the following errors while doing restoration of a database (TDE Enabled) from one SQL Server Instance to another SQL Server Instance.

Error 1: This error occurs when the certificate missing or wrong certificate on target server.

Cannot fine server certificate with tumbprint '0x1462B64F8DB084A1937D0BB9FB'

(Microsoft.SqlServer.SmoExtended)

Error 2: This error occurs when teh TDE has been enabled on the source database and missing the same MASTER KEY and Certification on target server.

The file "dbname_log" failed to initialize correctly. Examine the error logs for more details.

Error: 3283, Severity: 16, State:1

Error 3: This error occurs if the backup of certificate not used any Private Key file when it is backing up.

Msg 15507, Level 16, State 1, Line 3

A key required by this operation appears to be corrupted.

Msg 3013, Level 16, State 1, Line 3

RESTORE DATABASE is terminating abnormally.

Steps to restore a database (TDE enabled) from one SQL Server instance to another SQL Server instance:

Step 1: Backup the MASTER key from the source server as shown below. You can use any strong password. Note down the password that you are using for backup of MASTER key since we should use the same password at the time of restoration of MASTER key on target server.

USE master

GO

BACKUP MASTER KEY

TO FILE = 'D:\DatabaseFiles\Backups\masterkey.bak' 

ENCRYPTION BY PASSWORD = 'my@1!password' ;

Step 2: Backup the Certificate to a file from the source server using a private key file, that private key file will be encrypted by a password. The private key file (certikey.key) will be automatically created along with backup of certificate.

USE master

GO

BACKUP CERTIFICATE mycertificate

TO FILE = 'D:\DatabaseFiles\Backups\mycertificate.cert'

WITH PRIVATE KEY (FILE = ''D:\DatabaseFiles\Backups\certikey.key'

, ENCRYPTION BY PASSWORD = 'my@1!password') ;

Step 3: Take the FULL backup of database from the source server.

USE master

GO

BACKUP DATABASE TDETest TO DISK = ''D:\DatabaseFiles\Backups\TDETest.bak'

Step 4: Create MASTER KEY on the target system with password (Better to give the same password that you used in source server, it may avoid confusion)

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my@1!password' ;

Step 5: Restore the MASTER KEY on the target system from the backup that was taken in Step 1.

USE master

GO

RESTORE MASTER KEY

FROM FILE = 'D:\DatabaseFiles\Backups\masterkey.bak'

DECRYPTION BY PASSWORD = 'my@1!password'

ENCRYPTION BY PASSWORD = 'my@1!password' ;

Step 6: Create the same certificate on target server that you have created in source server and restore the certificate from the backup of certificate (mycertificate.cert)

USE master

GO

CREATE CERTIFICATE mycertificate WITH SUBJECT = 'encryption practice';

GO

-- Drop the same certificate

USE master

GO

DROP CERTIFICATE mycertificate;

GO

-- Restore the Certificate from backup

USE master

GO

CREATE CERTIFICATE mycertificate

FROM FILE = 'D:\DatabaseFiles\Backups\mycertificate.cert'

WITH PRIVATE KEY (FILE = ''D:\DatabaseFiles\Backups\certikey.key'

, DECRYPTION BY PASSWORD = 'my@1!password') ;

Step 7: Restore the database from the backup.

USE master

GO

RESTORE DATABASE [TDETest] FROM

DISK = 'D:\DatabaseFiles\Backups\TDETest.bak'

WITH FILE = 2,

MOVE N'TDETest' TO N'D:\DatabaseFiles\TDETest.mdf'

, MOVE N'TDETest_log' TO N'D:\DatabaseFiles\TDETest_log.ldf'

, NOUNLOAD ,STATS = 5

GO

-- Now your database should be restored successfully on the target server

Step 8: If you want to disable the TDE on target database and drop the MASTER KEY and Certificate from MASTER database on target server, you can follow the steps below.

-- Disable the TDE on the database

Right click on the database --> Go to Tasks --> Click on "Manage Database Encryption"

--> Deselect the option "Set Database Encryption ON" --> Click OK

-- Drop Certificate and Master Key from master database on target server

USE master

GO

DROP CERTIFICATE mycertificate;

GO

DROP MASTER KEY;

GO

Thanks

Chreddy SQLTrainer,

Crystalspiders Institute,
www.crystalspiders.com
contactus@crystalspiders.com
chreddy@hotmail.com
+91 733 845 7517
Whatsapp: +919553137753