Close Menu

Encryption

 

Moving a TDE Encrypted database to a different instance

• Published on august 1, 2017


Before You Proceed we recommend you to
read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.

Why Do I need to Move the database to a different Instance

Moving or restoring an TDE eanbled encrypted database could be of different reason.

  1. MIgration
  2. Disaster Recovery
  3. Upgradation from one version to another

IMPORTANT Please Be advised that if a database is encrpted by TDE ans associated with a key and a cetificate it needs to have both the key and cetificate to

Moving a TDE Enabled Database to a different Instance

If we look at the previous page I have a database named 'xxxvi' in my instance for which TDE is enabled.
now if you want to transport the database to a different server or instance and try to restore it just doesnt allow you to restore Hence you need to follow the below steps

To make it easier I have created a folder named backup on "D" drive and moved The files from the previous instance to the current instance.
The files I moved are

  1. XXXVI.bak
  2. XXXVI_DB_Cert.cer
  3. XXXVI_DB_Cert_Key.pvk

My files location is as follows

"D:\backup"

Step 1: Create master key on the new instance where the database needs to be restored

USE master 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NeP@$$w04#' 
GO -- we have created  a Master Key in the New instance

Step 2: Create certificate from the previously saved certificate

CREATE CERTIFICATE XXXVI_DB_Cert WITH SUBJECT = 'XXXVI_DB_Certificate';
GO -- we have created  a certifcate now

Step 3: Restoring the credentials

USE master
GO
CREATE CERTIFICATE XXXVI_DB_Cert
FROM FILE='D:\Backup\XXXVI_DB_Cert.cer'
WITH PRIVATE KEY(
FILE='D:\Backup\XXXVI_DB_Cert_Key.pvk',
DECRYPTION BY PASSWORD='XXXVIT3stD@8@Pw6')
GO
--I am using a different password here 

Step 4: Restoring the Database

USE master 
GO
RESTORE DATABASE [XXXXVI]
FROM DISK = N'D:\Backup\XXXVI.bak'
GO

Step 5: Verification

--Check Certificates
USE master 
GO 
SELECT *FROM sys.certificates WHERE pvt_key_encryption_type <> 'NA' 
GO

--Check Encryption Key Details for 2008
USE master
GO
SELECT key_length, key_algorithm, encryption_state, create_date 
FROM sys.dm_database_encryption_keys
GO

--Check Encryption Key Details for 2012 and further versions
USE master
GO
SELECT encryptor_type, key_length, key_algorithm, encryption_state, create_date 
FROM sys.dm_database_encryption_keys
GO

Continued to Next Page ------>> Disabling encryption and Removal of certificates and Keys