How can I duplicate a SQL Server symmetric key?

Question

We have a server with a database that has a symmetric key (Database → Security → Symmetric Key). We have backup copies of duplicate databases, which we use as test databases, but we do not have this key there.

How can I duplicate this symmetric key (or make a new one exactly the same as the old one) and put it into existing databases? It must have the same value and key name as another.

This is on SQL Server 2008.

Answer 1

, , KEY_SOURCE, IDENTITY_VALUE ALGORITHM.

, .

-- Create Database Master Key 
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Your Database Master Key Password here' 
GO

-- Create Encryption Certificate 
CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'Your Certificate Description Here' 
GO

-- Create Symmetric Key
CREATE SYMMETRIC KEY MyKeyName WITH
IDENTITY_VALUE = 'Enter a key description',
ALGORITHM = AES_256, 
KEY_SOURCE = 'Enter a key phrase here (keep very secret)'
ENCRYPTION BY CERTIFICATE MyCertificateName;

• IDENTITY_VALUE guid sys.symmetric_keys, .

• KEY_SOURCE , , .

• ALGORITHM, , sql- , .

script (, ), , .

, .

:

• Laurentiu Cristofor's