Sample Header Ad - 728x90

How do I generate the private key needed to restore the encrypted backup?

2 votes
1 answer
1184 views
I'm simulating the situation where I take a backup from instance **A** and restore it on instance **B**. I followed the doc to [Backup to Disk with Encryption](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-ver15#backup-to-disk-with-encryption) . Since I'm using only my desktop to simulate the whole process, after taking the backup of the database I took a backup of the certificate that was used as follows: BACKUP CERTIFICATE MyTestDBBackupEncryptCert TO FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert'; Then I dropped the MyTestDBBackupEncryptCert certificate and the database from my instance to simulate it is now instance **B**. I restored the certificate using the command from the [Creating a certificate from a file](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15#b-creating-a-certificate-from-a-file) doc with a slight modification where I commented the lines related to the PRIVATE KEY and DECRYPTION options since the certificate was encrypted by the MASTER KEY (well, that's what I understood from the first link): CREATE CERTIFICATE MyTestDBBackupEncryptCert FROM FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert' --WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', --DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00'); GO The certificate was created, but when I try to restore the database, I get this error: > Msg 15507, Level 16, State 30, Line 33
A key required by this > operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line > 33
RESTORE DATABASE is terminating abnormally. The documentation doesn't say how to get the private key needed to properly restore the certificate. How do I get it from SQL Server?
Asked by Ronaldo (6017 rep)
Apr 22, 2022, 01:10 AM
Last activity: Apr 29, 2024, 05:57 PM