How to restore encrypted backups

Posted in: Technical Track

SQL Server started having encrypted backups since SQL Server 2014. You can see the technical details here. This is a very good feature that allows you to encrypt the backups upon creation with different algorithms that will meet your security needs and requirements without having to use or pay for any third-party tool. Restoring the encrypted backups to its source server can be done without a hitch or change in process as with restoring normal backups. Some items to note and practices you might want to consider:

  1. Make sure you created the certificate with the correct expiry date. Take note that renewing the certificate, if it expires or extending the certificate’s expiration date, changes the thumbprint of the certificate. This makes it invalid for the backups already created using it before the changes and you won’t be able to restore those backups.
  2. Back up the certificate and keys and save it to a different location aside from the server where it was created. Take note that without the certificates, your backups will not be usable. It is best to keep it in an off-site location for your Disaster Recovery or Key Management providers.
  3. For Availability groups, make sure your certificates are all on the node. You normally run your backups on one of the nodes but these backups are for all your AG replicas. Make sure the certificate is available on all the nodes so you are always ready to restore it on any of the nodes.

For the last bit, it is part of the restore process for encrypted backups. You can only restore the backups on a server where the certificate that created it exists. If you try to restore the backups on a server without the certificate, you will receive the following error:

Msg 33111, Level 16, State 3, Line 20

Cannot find server certificate with thumbprint ‘0x9D8F11B623D5C7B94E64D1889ECCAD61C52A025D’.

Msg 3013, Level 16, State 1, Line 20

RESTORE DATABASE is terminating abnormally.RESTORE DATABASE is terminating abnormally.

You receive this error because you are missing an important part of the encrypted backup. Restoring backups to another server is a very common process. Be it due to migration or for the development/staging process, you are likely to restore the backups to another environment. Here are the steps to restore an encrypted backup to another server.

Backing up the backup certificate

You guessed it. You are receiving that error because the certificate does not exist on the server you are trying to restore it to.  All you have to do is import the certificate you used to create the backup to the server you are trying to restore it to. If you do not have the certificate file, you can back up the certificate to a file by following the instructions below:

  1. Log in to the source server in SSMS.
  2. First verify the certificate name by listing the certificates on the server.

select name,thumbprint from sys.certificates

  1. You’ll see the thumbprint for the certificate that matches the one from your error is the one you need. You can also check this from the backup file itself. Restore the header only of the file and you can find the column Encryptor_thumbprint for the information.

restore headeronly from disk=N’C:\SQLServer\yoyo_encrypted_backup.bak’

  1. Once you have the certificate name, back up the certificate.

Backup Certificate Backup_Certificate to file=’C:\sqlserver\Backup_Certificate.cer’ with Private key (File=’c:\sqlserver\Backup_Certificate.pky’,Encryption by password=’ScottTiger’)

 

Restoring the backup certificate

After backing up the certificate, you are halfway there. Or is it one third? Anyway, to be able to restore the encrypted backups on a different server, you’ll need to restore the backup certificate used to create it. To be able to create the certificate on the destination server, the master key on the server needs to exists. It doesn’t need to match the master key of the source server, so there is no need to recreate it if it already exists. Follow the steps below to check and restore the backup certificate.

 

  1. Check if the master key exists on the new server.

select name,is_master_key_encrypted_by_server from sys.databases

 

  1. If master database shows up, 1 on is_master_key_encrypted, skip the next steps and proceed to step 4.
  1. If the master database shows up 0 on is_master_key_encrypted, this means the master key still does not exist on this server. To create one run the command below.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MasterPassword’

  1. Once the master key is created, we can restore the backup certificate on this server. You can either copy over the certificate and private key that you created in the previous section to the server, or make sure its location is accessible to the destination server.

CREATE CERTIFICATE [Backup_Certificate] AUTHORIZATION [dbo]

FROM FILE = ‘C:\sqlserver\Backup_Certificate.cer’

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘ScottTiger’

, FILE = ”c:\sqlserver\Backup_Certificate.pky)

 

  1. Once this is completed you are now ready to restore encrypted backups. You can restore it as you would any backups through the GUI, TSQL, powershell or sqlcmd whatever you are most comfortable with.
email

Author

Interested in working with Pio? Schedule a tech call.

About the Author

Lead Microsoft Data Platform Consultant
Pio Balistoy is a Microsoft MVP for Data Platform from Singapore. He has been a Database professional for more than 17 years. He brings his passion for SQL to the community by being one of the Community leads for both Philippine Data Platform Forums (formerly Philippine SQL Server User Group) and Singapore SQL PASS.

2 Comments. Leave new

Pio :-

It appears the editor over-typed for you.

This

”c:\sqlserver\Backup_Certificate.pky

should read
c:\sqlserver\Backup_Certificate.pky’

Nice one family.

Reply

Pio :-

I think there is a duplicated “RESTORE DATABASE is terminating abnormally”, as well.

RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE is terminating abnormally.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *