unable to restore specific backup file

Question

I periodically need to restore and process backups from Microsoft CRM databases from outside our organization. I have two such databases, was told they were hosted on SQL 2016 Enterprise. I prepared a SQL 2017 Developer instance to take these in. One is about 33GB, one is about 180GB. The 180GB one is significantly larger than previous files, but I'm not sure that's the problem . I was able to restore the 33GB backup file without any issues using Restore Files and Filegroups in SSMS. But after I select the 180GB file, it does not then appear in the 'Select Backup Sets to Restore' list in the Restore window.

The data drive has plenty of space, but the system drive only has 109GB available. The log drive has about 18GB available. Is that the issue with this larger backup file? I'm not getting any helpful errors when I select it.

Thanks in advance,

spud

Answer 1

Hi @spudchick ,

> It appears they have either disabled TDE for previous backups

The restore will failed even though TDE is disabled before performing the database backup on the source server. In order to complete a successful restore of the database, you need to copy the certificate and the private key that you backed up earlier on the source server to the destination server.

Please refer to the blog How to restore a SQL Server database to another server after disabling TDE to get detail information.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi,

The larger one immediately failed with a "Cannot find server certificate with thumbprint" error.

Well... This why we needed to get the error message and now we know what the issue is.

The source of the issue is probably as @Robbie Varn explain = your database is using Transparent Data Encryption (TDE) and you will not be able to restore it until you get the Certificate, the Private key and the password.

ask the people who gave you the backup of the data to also provide the backup of the CERTIFICATE and the private key and don't forget to encrypt the backup with password. The following command will create two files (cert and key)

BACKUP CERTIFICATE Certificate_name TO FILE = '<path to backup of certificate file>'                 -- Location and name  
    WITH PRIVATE KEY (                                                                                     -- We need the backup of the PRIVATE KEY as well  
        -- DECRYPTION BY PASSWORD = '<add a complex password here>' ,  -- If the certificate is encrypted by a master key, this is not needed.  
        FILE = '<path to private key backup file>' ,                                              -- Location and name of the backup file we create for the private key file  
        ENCRYPTION BY PASSWORD = '<Password!For@The#File>'                  -- Password used to encrypt the private key  
    );    
GO  

Once you get these, then you can continue with the following steps in order to restore the database:

(1) Connect the master database using sysadmin LOGIN.

(2) BACKUP DATABASE before you start

(3) Create master key in the master database

USE master  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MyComplexPassword>'  
GO  

(4) Create CERTIFICATE in the master database, using the backup files of the certificate and private key

USE MASTER  
GO  
CREATE CERTIFICATE MyServerCert   
    FROM FILE = '<path to backup of certificate file>'  
    WITH PRIVATE KEY (  
       FILE = '<path to private key backup file>',  
       DECRYPTION BY PASSWORD = 'StrongPasswordgoeshere'   
    );  
GO  

Once this is done and executed you will be allowed to restore the database.

Answer 3

Thanks Pituach. I should mention that we will not be using the backup with the original client, we analyze it and extract what is required before wiping the original. It appears they have either disabled TDE for previous backups or have only recently enabled TDE in their application. Since this is a final copy before reset of a database, I'd like to ask them to give us a backup without TDE enabled. Do you have a recommendation for doing so?

Answer 4

I don't see an option for withdrawing this question, but I no longer need assistance with this topic. I appreciate the suggestions and advice.