Migrate Encrypted Database to a New Server (with a new key)


 
/* Migrating SQL Server Databases that use Database Master Key
--http://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

--Step 1:
--Find databases:  Database Master Keys encrypted by the Service Master Key:

select name
from sys.databases
where is_master_key_encrypted_by_server = 1

--Step 2:
/*----Add one new password for DMK in the source server database
-- If you don’t know a valid password for the Database Master Key, you can create a new one. 
--(! Multiple passwords can encrypt the same Database Master Key)
*/
 
--USE [theDB]
--GO
 
--alter master key 
--add encryption by password = 'myPass1d2f3wStrong'
--go


--Step 3:
--Backup  theDB and restore it to a new SERVER.
--3-1:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
drop encryption by service master key
go

--3-2:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
add encryption by service master key
go




*/

  


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s