SQL 2008 encryption - Database Transparent Data Encryption (2008 New)

-- 4-32

use master

-- create server level master key
create master key encryption by
password='password'
go

--drop master key

-- create server level cert
-- master->security->certificates
create certificate NorthwindCert
with subject = 'test'

--drop certificate NorthwindCert

use Nwind
go

--drop database Northwind

-- create db enc key with cert NorthwindCert
--會出現要你備份cert的資訊
--到此 database Nwind 已經不能直接resotre 到別的機器上了
create database encryption key
with algorithm=AES_128
encryption by server certificate NorthwindCert

--drop database encryption key
--db->右鍵->task->manage database encryption

alter database Nwind
set encryption on

alter database Nwind
set encryption off

--下面的指令:
-- encryption_state
-- 1: encryption off
-- 2: encryptioning
-- 3: encryption on
select * from sys.dm_database_encryption_keys
where database_id =DB_ID('Nwind')

backup database Nwind to disk='c:\backups\nw.bak' with format
backup log Nwind to disk='c:\backups\nw.bak'



--備份憑證
use master
go
backup certificate NorthwindCert
to file = 'c:\backups\bbb.cer' with private key (file='C:\backups\MyServerCertKey\aaa.key',encryption by password='password')


--BACKUP CERTIFICATE NorthwindCert TO FILE = 'c:\backups\test.cer';
--GO


--change connection to I2

--沒有匯入key以前...restore db會失敗
drop master key
create master key encryption by password='password2'

backup certificate NorthwindCert
to file = 'c:\backups\bbb.cer' with private key (file='C:\backups\MyServerCertKey\aaa.key',encryption by password='password')



--drop certificate NorthwindCert

CREATE CERTIFICATE NorthwindCert
FROM FILE = 'c:\backups\bbb.cer'
WITH PRIVATE KEY (FILE = 'C:\backups\MyServerCertKey\aaa.key',
DECRYPTION BY PASSWORD = 'password');
GO

--沒匯入憑證前
--Cannot find server certificate with thumbprint '0xAB32127B86A7D9B354F823F7B2AF307DD8BCE623'.

restore database Nwind from disk ='c:\backups\nw.bak' with
move 'Nwind_P' to 'c:\backups\nw.mdf',
move 'Nwind_A' to 'c:\backups\nw_a.ndf',
move 'Nwind_B' to 'c:\backups\nw_b.ndf',
move 'Nwind_C' to 'c:\backups\nw_c.ndf',
move 'Nwind_log' to 'c:\backups\nw_log.ldf',norecovery,file=1
,replace


restore log Nwind from disk ='c:\backups\nw.bak' with
move 'Nwind_P' to 'c:\backups\nw.mdf',
move 'Nwind_A' to 'c:\backups\nw_a.ndf',
move 'Nwind_B' to 'c:\backups\nw_b.ndf',
move 'Nwind_C' to 'c:\backups\nw_c.ndf',
move 'Nwind_log' to 'c:\backups\nw_log.ldf',recovery,file=2
,replace