I suppose you refreshed the testdb by using Restore database from production backup.

You will need to add the prod db key to your test server

(maybe have a look at my SSC article on Service Broker http://www.sqlservercentral.com/articles/Service+Broker/2897/)

cfr

USE Airline

GO

select *

from sys.certificates

;

select *

from sys.master_key_passwords

;

if not exists ( select MKP.*

, D.name as DbName

from sys.master_key_passwords MKP

inner join sys.credentials C

on MKP.credential_id = C.credential_id

inner join sys.database_recovery_status DRS

on MKP.family_guid = DRS.family_guid

inner join sys.databases D

on DRS.database_id = D.Database_id

Where D.name = 'Airline'

)

begin

Print 'Adding dbmasterkey for Airline';

-- using the same password that has been used the first time !

EXEC sp_control_dbmasterkey_password @db_name = N'Airline',

@password = N'P@ssword', @action = N'add';

end

GO

select MKP.*

, D.name as DbName

from sys.master_key_passwords MKP

inner join sys.credentials C

on MKP.credential_id = C.credential_id

inner join sys.database_recovery_status DRS

on MKP.family_guid = DRS.family_guid

inner join sys.databases D

on DRS.database_id = D.Database_id

;