When restoring a database you might get the below error.
System.Data.SqlClient.SQLError: Cannot find server certificate with thumbprint
This is because the database was encrypted with Transparent Data Encryption (TDE) and you will not be able to restore it until you get the Certificate, the Private key and the password from the supplier of the database.
After you collect the required items above, open a new SQL query as the server admin on the database master.
First we need to create the master cerificate on the server by using
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MyComplexPassword>'
Now that the master certificate has been installed you will need to install the certificate provided by the owner of the database
CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\TDECert\Cert.cer'
WITH PRIVATE KEY (FILE = 'C:\TDECert\key.pvk',
DECRYPTION BY PASSWORD = '<PasswordProvidedByTheSupplier>');
Once this is done and executed you will be allowed to restore the database.
When restoring a database from an SQL backup .bak, one usually creates a database and then selects the restore function. As soon as you try to restore the database you get the error saying “
The backup set holds a backup of a database other than the existing“. This is because it fails to read the files from the restore and matching them to the newly created files.
The error says:
Restore failed for Server ‘SQLSRV01’. (Microsoft.SqlServer.SmoExtended)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set hold a backup of a database other an the existing ‘test_restore’ database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
To solve this one should do the following:
– Don’t create an empty database and restore the .bak file on to it.
– Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.
This should allow you to restore the database with no error and fix the error 3154.