Hi

I have an sql server 2005+sp2 instance and a db on it.

I have create a database master key (specifying a password so it WILL NOT get encrypted by the server master key) on my database and backed it up



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test'

go

BACKUP MASTER KEY TO FILE = 'c:\Genesis_DBMasterKey.key' ENCRYPTION BY PASSWORD = 'test'





Then I have created a asymmetic key without specifying a password therefore the key will get encrypted bt the database master key.



CREATE ASYMMETRIC KEY card WITH ALGORITHM = RSA_512





And then for test I run



DECLARE @Encryptvalasym varbinary(MAX)

SET @Encryptvalasym = EncryptByAsymKey(AsymKey_ID('card'), 'EncryptedData')

SELECT @Encryptvalasym

SELECT CONVERT(varchar(max),DecryptByAsymKey(AsymKey_ID(' card'),@Encryptvalasym) )





And the result is OK.



Problem: as a recovey plan if I restore the database on the same or other instance (althogh the asymmetic key is part of backup) the decrypt doesn’t work.

I have tried to create a bank database restore tha databse master key and recreate the asymmetric key, but I’m afraid I’m still not having any luck with it, as it does not decrypt.



My main issue is that is any case if I have to recover the database , how do I have to make sure that encryption/Dycription will work?

Thanks