Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have taken backup from the live environment and trying to use it on the local box. The database in the live is encrypted with a key.

I have restored the database to localbox and cannot run any stored procedures as I have the following issue

Please create a master key in the database or open the master key in the session before performing this operation.

could anybody have faced this issue and how did you managed to solve. could you provide the answer step by step ?

I know the passowrd for the encrption on the live DB.

share|improve this question
    
The Server is SQL 2008 on live and 2005 on local box –  Hari Gillala Apr 13 '11 at 16:05
2  
If live server is 2008, there is no way you can restore in on 2005. Your local server must be also 2008, or the live server is 2005. Once upgraded to 2008 format, a database cannot be downgraded to 2005. –  Remus Rusanu Apr 13 '11 at 18:17
add comment

migrated from stackoverflow.com Apr 13 '11 at 17:34

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 6 down vote accepted

The database in the live is encrypted with a key.

I assume by that that you mean the database is using Transparent Database Encryption. The process of moving a TDE database to another server is described at Moving a TDE Protected Database to Another SQL Server, and it involves explicitly moving the database encryption key by doing a backup of the certificate used to encrypt the TDE key, which was previously backed up from the live server:

On the live server:

BACKUP CERTIFICATE ...
TO FILE = '...'
WITH PRIVATE KEY 
(
    FILE = '...',
    ENCRYPTION BY PASSWORD = ...'
);
GO

On the local server:

CREATE CERTIFICATE ...
FROM FILE = '...'
WITH PRIVATE KEY 
(
    FILE = '...',
    DECRYPTION BY PASSWORD = '...'
);
GO

With the certificate in place the local SQL Server will be able to decrypt the TDE database.

If you are not talking about TDE, then you need to explain what do you mean by 'the database is encrypted with a key'.

share|improve this answer
    
Does this same process apply to cell-level encryption as well? I have a similar question open. –  John Bubriski Feb 2 '12 at 19:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.