What is the point in taking encrypted backup (SQL Server 2014: Encrypted Backups), if we are not sure how to restore them. This blog is about taking a cue from the previous post and we will detail on the steps to restore the same.
Please follow the steps we used in the previous blog – SQL Server 2014: Encrypted Backups as we plan to continue using the same files for restore process in this post.
Restore an Encrypted Backup – Error 33111
If you take an encrypted backup and restore onto a new separate server, it will Error with the following message.
Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ’0x210E6D33163FE11C581911873F04857C8EBFC0BD’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
As the error suggests, this is a classic case of the certificates missing on this new server. Remember the step of taking a backup of our certificates in our previous article. Now it is going to help us.
Restore the Certificate
The command to restore the same is:
CREATE CERTIFICATE BackupCert
FROM FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’
WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,
DECRYPTION BY PASSWORD = ‘MyCertPassword-2014′ );
restore Encrypted Backup
The restore statement is same irrespective of encryption or not. The command in our case would look like:
RESTORE DATABASE [MySecureDB]
FROM DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’
WITH FILE = 1,
STATS = 10
It is as simple as it gets. One of the other enhancement that I mentioned before about messages while restore or backup can also be read – SQL Server 2014: Backup Informational messages.