Backup and Restore TDE Encrypted Database

This topic explains how to back up and restore the TDE encrypted database.

This is for the primary SQL Server TDE VM. If you need to restore a TDE encrypted database from an alternate/failover VM, see Restore TDE Encrypted Database on an Alternate (Failover) VM .

If you are backing up a database that uses cell encryption keys, you should ensure that all sensitive data is encrypted first before back-up commences. Before back-up, remove the cell encryption key references from the database itself. If key references are not removed from the database, they will be stored within the database back-up. This should be avoided from a security point of view. If you are backing up a database that is both cell and TDE encrypted, perform the above instructions for the cell encryption keys before continuing with the following instructions for backing up a TDE encrypted database.

When backing up a TDE encrypted database, you must have the TDE credential and database wrapping key (TDEKEK) present.

Once you have prepared the database as described above, you may back-up the database in a similar manner to an unencrypted database. If you are backing up a TDE encrypted database, it will be backed up while remaining in its encrypted form, which is advantageous from a security point of view.

Your backup will include data content of your selected database, but may not include backups of SQL Server logins or credentials. For further details, see the Microsoft SQL Server documentation. Otherwise, when later restoring the database, you may have to recreate suitable SQL Server logins and credentials, although this should not be a difficult task.

Backing up TDE database using SQL Server Management studio

This provides a basic example of how to backup a database. Please refer to Microsoft SQL Server documentation for a more thorough treatment of backup (and restoration) of a database.

  1. In SQL Server Management Studio, navigate to Management.

  2. Right-click on Management and select Back up.

  3. Set Database_Name using the pull down menu.

  4. Set Backup type as Full using the pull down menu.

  5. Set Backup component button as Database.

  6. Under Destination select Disk.

    Note: Click Remove to set aside any previously named back-up file(s) that you do not want to keep. Click Add and provide a suitable path and name for the backup file, e.g. <Drive>:<Backup_directory_path>\TestDatabase_TDE_[date].bak.
    Press OK to accept the file path and name. Press OK again. You must remove the existing entry as backup only allows a single entry to populate this field at any one time. Make sure that you rename with a meaningful and unique name for the backup and include the bak suffix.

  7. When the back-up is complete, the message The backup of database 'TestDatabase' completed successfully is displayed. Press OK.

  8. Make sure you can access the back-up file at the location given above.

    Note: If the database back-up fails with a message indicating that the transaction log is not up to date, repeat the above steps, but for step 4 select Backup type as Transaction Log. In step 6, provide a suitable Log file name. After this completes successfully, you should be able to perform the database back-up.

Backing up TDE encrypted database with SQL script

The TDE encrypted database can be backed up and restored, provided the TDE EKM access is available to the same Keys from the SQL Server VM where you are trying to restore the database.

If you are restoring to the same VM there is no new procedure addition, just follow the backup / restore workflow specified by Microsoft SQL Server documentation.

The following set of SQL statements can be used to backup the encrypted database.

Copy 
BACKUP DATABASE [testdb] TO  DISK = N'D:\testdb.bak'

Restoring a TDE encrypted database

On same SQL Server VM the restore can be performed with the following SQL statements

Copy 
USE [master]
RESTORE DATABASE testdb FROM DISK = N'D:\testdb.bak' WITH FILE = 1, NOUNLOAD, STATS = 5

Open Old Versions of a TDE Key Using its Thumbprint

To open old versions of the TDE key with its thumbprint, you will need to specify the key name starting with @ followed by the thumbprint of the key. For example: 

Copy 
USE master;
CREATE ASYMMETRIC KEY TDE_KEY_v1 -- Give the master key version a name in SQL Server
FROM PROVIDER EKM_Prov WITH
PROVIDER_KEY_NAME = '@E5196A7752214754934DF7BD479B6F7E',   -- master key thumbprint
CREATION_DISPOSITION = OPEN_EXISTING;
GO