Restoring Encrypted Databases with VESQL

For export scenarios and for restore to the state before selected transaction, Veeam Explorer for Microsoft SQL Server uses a staging Microsoft SQL Server. This staging server must support and be able to read the encrypted database. In order for it to read the encrypted database, you must first restore the certificate protecting the Database Encryption Key. For more information on the staging server, see System Requirements for Veeam Explorer for Microsoft SQL Server.

Note: Transparent Data Encryption requires the Enterprise edition of Microsoft SQL Server; this also applies to the staging server.
 
If you are not exporting or restoring to a specific transaction, but you are restoring to a different SQL Server instance than the one you backed up from, that SQL Server is indicating that it is unable to read the encrypted database. You must first restore the certificate to the SQL Server before you can restore the database. 

You can identify the required certificate by the data listed in the error message, or by the certificate name and serial number displayed in VESQL:



To back up the certificate on the original SQL Server, use this query (replacing the paths, certificate name, and password):
USE master
BACKUP CERTIFICATE <certificate name> TO FILE = 'path_to_file'
WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='******', FILE='path_to_private_key_file');
 
To restore the certificate on the staging server or on the SQL Server to which you are restoring, use this query (replacing the paths, certificate name, and password):
USE master
CREATE CERTIFICATE <certificate name> FROM FILE ='path_to_file'
WITH PRIVATE KEY(FILE='path_to_private_key_file', DECRYPTION BY PASSWORD='******');
 
For example, if the Database Info indicates the required certificate name is MyServerCert:
USE master
CREATE CERTIFICATE MyServerCert FROM FILE ='C:\backups\certificate.cer'
WITH PRIVATE KEY(FILE='c:\backups\myservercertprivatekey', DECRYPTION BY PASSWORD='StrongPassword1234!');
 
If restoring the certificate fails with the error “Please create a master key in the database or open the master key in the session before performing this operation” you must run the following query (replacing the password):
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******'