Appendix A: Back up and restore Microsoft SQL Server databases

A common task for database administrators is backing up and restoring databases. But what if the backup is stored in a different location from the original database and needs to be restored there as well, especially when you apply Transparent Data Encryption (TDE)? This overview shows how to restore a database backup with TDE enabled to a different server from where you took the original backup.

Overview

When you perform a database backup using SQL Server Management Studio (SSMS), for example, the backup files are typically stored on the same server where the database is running. To perform a backup, you can right-click on the database instance, select the Tasks option, and then choose Backup. A window opens, prompting you to select the type of backup. You can choose whether to save the backup to a disk or specify a URL. This example stores the backup file on a disk, with SSMS automatically configuring the file path. The backup file has a .bak extension. You can view information about the backup file by running the following SQL query:

Sql

RESTORE FILELISTONLY

FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\exampleDB.bak';

In the output, one of the columns displays the backup file information. If you enabled TDE for the database instance, you also see the TDE thumbprint in that column.

If you want to restore the backup file on a different database server or engine, you must configure both the original database server or engine and the second server or engine as endpoints on CryptoHub.

When following the original instructions to set up TDE on your primary database, you need to update the EKM credentials only when setting up TDE on the second server. On your secondary database server or engine, send the following SQL Query:

Sql

CREATE CREDENTIAL EkmCredential
    WITH
       IDENTITY = 'Identity name for your secondary DB server/ engine' ,
       SECRET = 'Password for your secondary Db server/engine'
    FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO

The key name of the RSA key generated for TDE must be identical to the RSA key name used on the primary database from which you took the backup.

Sql

USE master;  
GO

CREATE ASYMMETRIC KEY EkmAsym (-> RSA Keyname must overlap)
    FROM PROVIDER FxclEkmProvider
    WITH
        ALGORITHM = RSA_1024,
        PROVIDER_KEY_NAME = 'EkmAsym'; (-> RSA Keyname must overlap) 
GO

According to the instructions on CryptoHub, to set up TDE on SQL Server, you must complete all the steps until step 6 in the Enable Transparent Data encryption on the Microsoft SQL Server section. When restoring the original database on the secondary database server or engine, you complete the remaining steps.

Additional notes

When backing up your database instance, ensure that your secondary database instance has access to the directory where the backup files are stored. To verify this, perform the following steps to obtain the name of your secondary database engine or server:

Open the Services (services.msc).

Locate your database engine or instance and double-click it.

Go to the Log On tab to find the account name in the account section (for example, NT Service\MSSQL$HARISVMINSTANCE3).

Right-click the folder where your backup files are stored and select Properties,

Go to the Security tab and edit the permissions. Add the SQL Server service account for the secondary database instance and grant it full access rights.

​Overview

​Additional notes

Overview

Additional notes