Copy \ Move a Database that is encrypted with TDE

Testing TDE without Certificate in the Instance

In the previous article I’ve encrypted the database TDE_Testing, lets try to use the physical files of this database with different methods to restore \ attach in another instance which doesn’t have this certificate and lets check whether SQL Server 2008 detects this.

Backup and Restore Method

Lets take the backup of TDE_Testing database which is encrypted with TDE

 

Now we have the backup of encrypted database lets try to restore it in a instance which don’t have the certificate. SQL Server 2008 has immediately thrown the error couldn’t find the certificate.

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ’0x10B56404F499B92B8A372B6B05D44D283A88F723′.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
 

Hence if you encrypt the database with TDE you should have the certificate backup with the database backup then only you will be able to restore it in different instance.

Detach \ Attach Method

Ok the first method gets failed lets try to detach the database, copy the physical files of the database to another instance and will attach it there. I’ve already detached the database and moved to an another instance lets try to attach it.. Yes you are right SQL Server 2008 detects the TDE and returns the below error

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ’0x10B56404F499B92B8A372B6B05D44D283A88F723′.



 

 

Hence if you encrypt the database with TDE you should have the certificate backup with the database physical files then only you will be able to attach it in different instance.

Compressed Backup and Restore Method

Hmmm, both the methods failed. Ok lets see whether SQL Server 2008 detects TDE on compressed backup. Now I’m going to take a compressed backup of TDE_Testing database and going to restore it in another instance.

 

Lets restore it.

SQL Server detects TDE in compressed backup too. So you to have the certificate backup.

Copy Database Wizard

Yeah we got another method to move the database, lets try to copy \ move the database using Copy database wizard into an instance which don’t have the certificate. Let’s see whether SQL Server detects it.

 

 

 

Great SQL Server 2008 detects this too, its not allowing me to move the database without the certificate. Hence the certificate is too important to copy \ move the database which is encrypted with TDE.

Import \ Export Wizard

Ok Lets all the method above failed, lets try to use Import \ Export wizard to move the data’s from one instance to another instance.

 

 

This worked for me.. You know why this worked, because this is out of TDE architecture and TDE doesn’t encrypt the data’s which is residing at buffer pool or in memory hence when you use Import \ Export wizard data’s will be retrieved from disk to buffer pool hence those data’s are not encrypted and its transferred to another instance without encryption hence this gets succeeded.

Ok, now we have tested almost all the possibilities to move the physical files of the database from once sever to another server which is encrypted with TDE and all the possibilities got failed. From this you know how important is to backup the certificate, this is because if the instance is crashed then without this certificate you can’t recover the database.

Lets discuss the proper method to move \ copy the database which is encrypted with TDE.

Move \ Copy a database which is encrypted with TDE

To copy or move a database which is encrypted with TDE you need to create the certificate in the destination instance first from the backup of the certificate created. In this case I’ve created the certificate backup while writing my previous article, lets use that backup and restore it in the destination instance.

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEtesting123'

GO

CREATE CERTIFICATE TDE_Testing

    FROM FILE = 'C:\Users\Sagar\Documents\TDE_testing_cert_backup'

    WITH PRIVATE KEY (FILE = 'C:\Users\Sagar\Documents\TDE_testing_key',

    DECRYPTION BY PASSWORD = 'TDEtesting123');

GO

 

 

 

Now we have restored the certificate in the destination instance, now lets try to attach & restore the database, this should now work.

Detach \ Attach Method

After the certificate is restored we are now able to attach the database.

Backup \ Restore Method

Great this too worked lets see whether the database is encrypted in the destination server.