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
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?
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
Last activity: Apr 29, 2024, 05:57 PM