If you have worked with SQL Server for a given period of time chances are that you have experienced, witnessed, or at least familiar with the concept of backing up a database. This task is a primary staple in the DBA diet and should always include verification that the backups actually are valid.

An example of a statement that can be used to backup a SQL Server database is:

BACKUP DATABASE [DemoTDE]
    TO  DISK = 'C:\Backup\DemoTDE.bak'
    WITH NOFORMAT,
    INIT, 
    NAME = 'DemoTDE-Full Database Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD, 
    STATS = 10
GO

When Transparent Data Encryption (TDE) is enabled on a database there are some additional dimensions that must be considered when performing a database backup.

BACKINGUP TDE ENABLED DATABASES
In my previous blog entry titled TDE: Under The Hood With TempDB I included the steps that are required to implement TDE. These steps include the creation of a Database Master Key (DMK), which is protected by the Service Master Key (SMK), as well as a Certificate that is protected by the DMK. All of these items reside in the MASTER database and are not included in the backup of the user database in which TDE is enabled or the MASTER database. To perform a backup of these items the following statements must be executed:

BACKUP SERVICE MASTER KEY
    TO FILE = 'C:\Backup\DemoTDE_SMK.bak'
    ENCRYPTION BY PASSWORD = 'MySMKBackupP@ssWord2009'           
GO

BACKUP MASTER KEY
    TO FILE = 'C:\Backup\DemoTDE_DMK.bak'
    ENCRYPTION BY PASSWORD = 'MyDMKBackupP@ssWord2009'  
GO

BACKUP CERTIFICATE MyServerCert
    TO FILE = 'C:\Backup\DemoTDE_CERT.bak'
GO

With the execution of the BACKUP DATABASE command and the three statements above you will have all of the files required to successfully recover your TDE enabled database. Please note that backing up the MASTER database or TEMPDB database is not required for recovering a TDE enabled user database.

The general database backup process often includes transferring the backup files to an external medium and stored in a safe location. When encryption is involved it is recommended to store the key backup files on a separate medium and location from the database backup file. This practice ensures that if the medium that contains the database backup file falls into malevolent hands the contents of the data remains secure since the keys are required to recover and access the data.

There are no additional backup requirements for the Database Encryption Key (DEK) which was created on the user database in which TDE was enabled. The DEK is actually stored in the user database's boot record and is included in the database backup. When the database is recovered the database boot record is accessed and the DEK is available for reference at that time.

RECOVERING TDE ENABLED DATABASES
When it comes time to recover a TDE enabled database there are a few variants in the approach depending upon the level of recovery required. For example: When the entire instance is requiring recovery, or the recreation of an instance is occurring on a separate server, the recovery of the SMK, DMK and Certificate will be required. If the recovery effort is focused on a specific user database within the instance of its origin the recovery of the database backup will often be sufficient.

If the SMK recovery is required you will need to carefully consider recovery or altering of all items within the instance that are protected by the SMK. The recovery of the SMK is performed by the following statement:

RESTORE SERVICE MASTER KEY
    FROM FILE = 'C:\Backup\DemoTDE_SMK.bak'                   
    DECRYPTION BY PASSWORD = 'MySMKBackupP@ssWord2009'  
GO  

The recovery of the DMK is performed by executing the following statement in the MASTER database:

RESTORE MASTER KEY
    FROM FILE = 'C:\Backup\DemoTDE_DMK.bak'
    DECRYPTION BY PASSWORD = 'MyDMKBackupP@ssWord2009'
    ENCRYPTION BY PASSWORD = 'MyNewDMKP@ssWord2009'
GO

Please note that we recovered the DMK using the protection of a password (ENCRYPTION BY PASSWORD) rather than the SMK. This allows the DMK to be recovered even in an instance in which the SMK is different from the instance in which the DMK was originally created. To change the DMK back to being protected by the SMK, the following series of statements are required in the MASTER database:

OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'MyNewDMKP@ssWord2009'
GO

ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY
GO 

CLOSE MASTER KEY
GO

Please note that modification of the DMK requires it to be opened first.

Certificates are recovered through re-creation rather than recovery. If the Certificate used for TDE previously exists in the MASTER database it will need to be dropped prior to execution the following statement:

CREATE CERTIFICATE MyServerCert
    FROM FILE = 'C:\Backup\DemoTDE_CERT.bak'   
GO

At this point all of the keys that reside in the MASTER database have been recovered and the user database in which TDE has been enabled is ready to be recovered through the following statement:

RESTORE DATABASE [DemoTDE]
    FROM DISK = 'C:\Backup\DemoTDE.bak'
    WITH  FILE = 1, 
    NOUNLOAD, 
    REPLACE, 
    STATS = 10
GO

Once the database has been restored the success of its recovery can be determined through querying a table that resides within the database.

Disclaimer: All of the SQL Statements offered in this blog entry are provided as examples of their use and syntax. The arguments and their values used may vary depending upon your situation. Additional details regarding these statements can be obtained through SQL Server Books On Line. Always backup and test in a development environment before executing these suggested statements in a production environment.