1

Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use encrypted columns I had to:

  1. Create database master key;
  2. Create certificate;
  3. Create symmetric key.

Which of those are stored along with backup, and which I should backup manually and restore on the other server manually as well? I see there is SQL statements backup master key, restore master key, backup certificate, create certificate ... from file. So, along with database itself, there are:

  1. Master key;
  2. Master key password;
  3. Certificate;
  4. Certificate private key;
  5. Symmetric key.

So what (and how) should I deal with when I restore my database on a fresh server? Thank you!

Improve this question
2
  • Have you used TDE to encrypt data in the source system? Commented Apr 9, 2021 at 11:19
  • @M.Ali no, I use column encryption with EncryptByKey / DecryptByKey functions. Commented Apr 9, 2021 at 11:23

2 Answers 2

Reset to default
3

You are using column encryption. This is not always-encrypted. When you backup your data base and restore it, you only need to recover the master key. Anything else will be restored through the backup file.

You can restore the master key with this command:

USE [Your_DB_Name]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Your_MasterKey_Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
Improve this answer
0

To restore a TDE enabled backups on a different server, you need to do the following:

  1. Backup Certificate along with the databases on the source system
  2. Ensure there is already a master key on the destination server if not create one
  3. Create certificate on the target server from the Backup files (Certificate backup file and the private key file) from the source server
  4. Finally, restore backup and it should work.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.