Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.
As i mentioned earlier, we can’t restore a backup on a different instance without Certificates. We’ve to restore the Cert from Source Server , then we should be able to restore the Database from the Backup.
Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.
My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)
To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀
Soo…..what should we do???
All We’ve to do is restore our Certificate from source(And you have to create a DMK on destination if you don’t have one), Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.
On DR Instance:
Create a Master Key(You don’t have to do this if you already have DMK) and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'
GO
CREATE CERTIFICATE TDEDEMOCERT
FROM FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
DECRYPTION BY PASSWORD = 'VeryStrongPassword');
GO
/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
TO FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )
GO
*/
Now let’s do the actual restore and see how it goes….
Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database(You are safe as long as you can access the source database)!!!
MSSQLTREK 