Backup And Resore Encrypted Data

I have two databases, one production and other a production copy.

One column of the production table is encrypted by the following syntax.



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'



CREATE CERTIFICATE cert_demo

WITH SUBJECT = 'encryption demo'



CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE cert_demo

-----------------------------------

I have stored procedure to add records to a production table like this.

ALTER Procedure AddRecord

@id int,

@var1 varchar(100)

AS

BEGIN

DECLARE @var2 varbinary(256)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

SET @var2 = EncryptByKey(

key_guid( 'symkey_demo' ),

@var1 )

insert into dbo.Test(id,Data,EData)

values(@id,@var1,@var2)

CLOSE SYMMETRIC KEY symkey_demo

END

---------------------------------



Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.



Create procedure ViewData

@id int

AS

BEGIN

DECLARE @var1 varbinary(256)

DECLARE @var2 varchar(100)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

select @var1=EData from Test where id=@id

Set @var2 = convert( varchar(100), DecryptByKey( @var1))

print @var2

END



It doesn't print the enctypted data. Please suggest what goes wrong.