Knowledge Base Listing Page
Restoring Encrypted Databases with VESQL

Restoring Encrypted Databases with VESQL

KB ID:	2006
Product:	Veeam Backup & Replication 10, Veeam Backup & Replication 9.5, Veeam Backup & Replication 9.0, Veeam Backup & Replication 8.0
Published:	2015-02-10
Last Modified:	2020-12-28

Challenge

Restoring an encrypted database with Veeam Explorer for Microsoft SQL Server fails with one of the following errors:

Cannot find server certificate with thumbprint '<hex code>'
Transparent Data Encryption is not available in the edition of this SQL Server instance.

You are unable to check “Perform restore to the specific transaction” because of this error:

Fine tuning is not available (certificate “<certificate name>” does not exist on the target SQL Server).

Cause

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.

Solution

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:

User-added image

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 = '******'

More information

If you do not have a backup of the certificate and can no longer back it up on the original SQL Server, you can perform Instant VM Recovery and back up the certificate on the restored VM, then retrieve the certificate and private key files as described in KB1459.

If you need to restore the encrypted database files (*.mdf and *.ldf) in a way that bypasses VESQL, you can use Windows File Level Restore.

KB ID:	2006
Product:	Veeam Backup & Replication 10, Veeam Backup & Replication 9.5, Veeam Backup & Replication 9.0, Veeam Backup & Replication 8.0
Published:	2015-02-10
Last Modified:	2020-12-28

BACK TO KB SEARCH

Couldn't find what you were looking for?

Below you can submit an idea for a new knowledge base article.

TECHNICAL ISSUE

KB FEEDBACK/SUGGESTION

Report a typo on this page:

Please select a spelling error or a typo on this page with your mouse and press CTRL + Enter to report this mistake to us. Thank you!