Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Restoring a TDE Database on a New Instance

You’ve enabled Transparent Data Encryption (TDE) on one of your databases, and the server has failed. How do you get this database working on a new instance? This short post will show you how this works.

Files Needed

There are two files you need in order to restore the database.

  1. A full backup of the TDE database
  2. A backup of the server certificate that protects the Database Encryption Key (DEK).

You might have multiple files for the backup, and potentially other backup files (diff, log), but the process for those will be the same as any other restore once you complete this process.

If you have multiple full backup files (striped backup), just include them in the restore command as you normally would.

Prepare the New Instance

To prepare the new instance for restore, you need to ensure that you have a database master key (DMK) in the master database. You can do this by checking the master_keys DMV.

SELECT * FROM sys.symmetric_keys

 

If you have a key, that’s fine. If you don’t, you can create one like this:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sdkj3G3$sh'

Now you need to restore the certificate from your source instance. You do this with the CREATE CERTIFICATE command, and the FROM FILE option. You’ll typically find the backup from your BACKUP CERTIFICATE command in the DATA folder for SQL Server if you didn’t specify a complete path.

You do have a backup, right?

For me, I’ll run the create certificate command:

CREATE CERTIFICATE TDEPRimer_CertSecurity FROM FILE = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\tdeprimer_cert' WITH PRIVATE KEY ( FILE = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\tdeprimer_cert.pvk', DECRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%') ; go

 

This works

tde_a

Now I’m ready to restore the TDE database.

tde_b

I select my backup file, and everything proceeds as a normal restore. How do I know the certificate worked?

Because I have a “Ready” at the top of the dialog.

tde_c

If I didn’t have the certificate on the instance, I’d get this:

tde_d

Double clicking that would bring up the error:

tde_e

If the certificate is not on the instance, then the server cannot decrypt the DEK and restore the database.

Hope this helps, and if you use TDE, make sure you can do this.


Filed under: Blog Tagged: administration, backup, encryption, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...