Appendix A: Back up and restore Microsoft SQL Server databases

5min
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 Transparent Data Encryption (TDE) is applied? This overview shows how to restore a database backup with TDE enabled to a different server from where the original backup was taken.
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';
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 TDE is enabled 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 .
When following the original instructions to set up TDE on your primary database, you only need to update the EKM credentials 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
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 where 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
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
﻿
As per the instructions on , 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:
1
Open the Services (services.msc).
2
Locate your database engine or instance and double-click it.
3
Go to the Log On tab to find the account name in the account section (for example, NT Service\MSSQL$HARISVMINSTANCE3).
4
Right-click the folder where your backup files are stored and select Properties,
5
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.
﻿
Enable Transparent Data encryption on the Microsoft SQL Server
MongoDB