How To Restore TDE Keys and Database to a New or Different SQL Server

In this article, we will cover the specific steps to restore a set of TDE keys and a database to a new and or different server. This process is something you would need to do when a possible disaster happens with hardware which happened in my case a couple of weeks ago. A total active/passive cluster failure of twenty hard drives at once.

This article assumes you have already made a backup of your keys and databases, if you haven't done so yet, go ahead and do it right away, because if you don't have your keys, you will lose your data.

Step 1, locate your backup files and password.

Step 2, Restore your Master Key. Reference your backup file and then put enter in your passwords.

USE MASTER
GO
RESTORE MASTER KEY   
    FROM FILE = 'B:\TDEKeys\master_key.key'   
    DECRYPTION BY PASSWORD = '<yourSecurePassword>'   
    ENCRYPTION BY PASSWORD = '<yourSecurePassword>';  
GO

Step 3, Restore your Service Master Key. Reference your backup key file and then enter your secure password.

USE MASTER
GO
RESTORE SERVICE MASTER KEY   
    FROM FILE = 'B:\TDEKeys\service_master_key.key'
    DECRYPTION BY PASSWORD = '<yourSecurePassword>';  
GO

Step 4, Create your server certificate from your Certificate. Reference your backup cert file and then enter your secure password.

USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<yourSecurePassword>';
CREATE CERTIFICATE YourServerCertName FROM FILE ='B:\TDEKeys\YourServerCertName.cer'
    WITH PRIVATE KEY (FILE = 'B:\TDEKeys\ServerCertName.pvk',
    DECRYPTION BY PASSWORD = '<yourSecurePassword>');
GO

Step 5, Open the master key with your secure password, alter the master key, and add encryption by service master key.

USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<yourSecurePassword>';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

Step 6, Now you can restore your database full backup and transaction logs.

USE MASTER
GO
RESTORE DATABASE [YourDatabase] FROM  
DISK = N'X:\DatabaseBackups\Full\YourDatabase\YourDatabase_FULL_20200919_030000_1.bak',  
DISK = N'X:\DatabaseBackups\Full\YourDatabase\YourDatabase_FULL_20200919_030000_2.bak',  
DISK = N'X:\DatabaseBackups\Full\YourDatabase\YourDatabase_FULL_20200919_030000_3.bak',  
DISK = N'X:\DatabaseBackups\Full\YourDatabase\YourDatabase_FULL_20200919_030000_4.bak' WITH  FILE = 1, NORECOVERY, NOUNLOAD,  REPLACE,  STATS = 5
RESTORE LOG [YourDatabase] FROM  DISK = N'X:\DatabaseBackups\LOG\YourDatabase\YourDatabase_LOG_20200919_030101.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [YourDatabase] FROM  DISK = N'X:\DatabaseBackups\LOG\YourDatabase\YourDatabase_LOG_20200919_112000.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

Now you can query your data on your new server.