27,579
社区成员
发帖
与我相关
我的任务
分享
--Create a master key
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '~qaz123[]\';
GO
--Create a certificate
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
--Create a database encryption key and protect it by the certificate
USE TEST
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
--Set the database to use encryption
USE TEST
ALTER DATABASE TEST
SET ENCRYPTION ON;
GO
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
RESTORE DATABASE TEST
FROM DISK = 'E:\数据库\测试数据库\TEST.bak'
WITH MOVE 'TEST' TO 'E:\\数据库\测试数据库\TEST.mdf',
MOVE 'TEST_log' TO 'E:\数据库\测试数据库\TEST.ldf',
NOUNLOAD, REPLACE
--backup certificate nad primary key to file
USE master
GO
BACKUP CERTIFICATE MyServerCert TO FILE = 'D:\Test DB\Test\MyServerCert.cert'
WITH PRIVATE KEY (
FILE = 'D:\Test DB\Test\MyServerCertPrivate.key',
ENCRYPTION BY PASSWORD = '~qaz123[]\');
GO
USE master
GO
CREATE CERTIFICATE MyServerCert
FROM FILE = 'E:\学习\数据库\测试数据库\MyServerCert.cert'
WITH PRIVATE KEY (
FILE = 'E:\学习\数据库\测试数据库\MyServerCertPrivate.key'
, DECRYPTION BY PASSWORD = '~qaz123[]\')
GO
SELECT * FROM sys.certificates where name = 'MyServerCert'
RESTORE DATABASE TEST
FROM DISK = 'E:\数据库\测试数据库\TEST.bak'
WITH MOVE 'TEST' TO 'E:\\数据库\测试数据库\TEST.mdf',
MOVE 'TEST_log' TO 'E:\数据库\测试数据库\TEST.ldf',
NOUNLOAD, REPLACE