sqlserver配置备份加密

SQL Server 2014（企业版、标准版）及以后的版本，支持使用证书对备份文件进行加密，确保数据在备份存储介质上的安全。在第三方服务器上，如果没有恢复这个证书将无法恢复备份文件，且加密后的备份文件无法被旧版本的 SQL Server（向下不兼容）读取。

准备测试数据

--.创建数据库
USE master
IF DB_ID('demo') IS NULL
CREATE DATABASE demo
GO

--.初始化数据表
USE demo
IF OBJECT_ID('dbo.tb_user', 'U') IS NOT NULL
DROP TABLE dbo.tb_user
GO

--.创建数据表
CREATE TABLE tb_user(
  id bigint IDENTITY(1,1) NOT NULL,
  userid bigint NOT NULL,
  username nvarchar(10) NOT NULL,
  createtime datetime NOT NULL DEFAULT (getdate()),
  PRIMARY KEY CLUSTERED (id ASC))
ON [PRIMARY]
GO

--.插入测试数据
INSERT INTO tb_user(userid,username) VALUES (10001,'joy')
GO 

--.查看数据
SELECT * FROM tb_user

配置备份加密

• 第1步，创建主密钥和证书

USE master
GO

--.创建数据库主密钥
-- select * from sys.symmetric_keys where name ='##MS_DatabaseMasterKey##' 
-- DROP MASTER KEY 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passw0rd_123'

--.创建用于备份加密的证书
-- select * from sys.certificates where name = 'BackupEncryptCert'
-- DROP CERTIFICATE BackupEncryptCert 
CREATE CERTIFICATE BackupEncryptCert WITH SUBJECT = 'Certificate for Backup Encryption'

• 第2步，备份主密钥和证书

注：一旦用于加密备份的证书丢失或损坏，备份文件将永远无法还原，请将证书和主密钥备份到安全位置

USE master
GO

--.创建备份目录
EXEC master.sys.xp_create_subdir 'C:\CERTIFICATE'

--.备份主密钥
BACKUP MASTER KEY TO FILE = 'C:\CERTIFICATE\master_key.key' ENCRYPTION BY PASSWORD = N'Passw0rd_123'

--.备份证书
BACKUP CERTIFICATE BackupEncryptCert TO FILE = 'C:\CERTIFICATE\BackupEncryptCert.cer' 
WITH PRIVATE KEY (FILE = 'C:\CERTIFICATE\BackupEncryptCert.pvk', ENCRYPTION BY PASSWORD = N'Passw0rd_123')

• 第3步，执行加密备份

--.执行加密备份
BACKUP DATABASE demo
TO DISK = 'C:\demo.bak'
WITH 
  COMPRESSION,
  ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = BackupEncryptCert
  ),
STATS = 1;

恢复测试

• 在另外一台机器（若无证书）上执行 RESTORE DATABASE 恢复数据库，则会报错如下

--.恢复数据库
RESTORE DATABASE demo FROM DISK = 'C:\demo.bak'

--.报错如下
消息 33111，级别 16，状态 3，第 1 行
找不到指纹为 '0x919F1BE0F2240DC9794E25B82FA9E52DEBF41C46' 的服务器 证书。
消息 3013，级别 16，状态 1，第 1 行
RESTORE DATABASE 正在异常终止。

• 此时需要先恢复主密钥和证书，然后再次恢复

USE master 
GO 

--.创建数据库主密钥
-- select * from sys.symmetric_keys where name ='##MS_DatabaseMasterKey##' 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Passw0rd_123'

--.还原证书
-- select * from sys.certificates where name = 'BackupEncryptCert'
CREATE CERTIFICATE BackupEncryptCert FROM FILE = 'C:\BackupEncryptCert.cer' 
WITH PRIVATE KEY (FILE = 'C:\BackupEncryptCert.pvk', DECRYPTION BY PASSWORD = N'Passw0rd_123')

--.再次恢复数据库
RESTORE DATABASE demo FROM DISK = 'C:\demo.bak'

--.确认数据
SELECT * FROM demo..tb_user

已废除的 WITH PASSWORD

• 在 SQL Server 2005 / 2008 中，可以通过参数 WITH PASSWORD 为备份文件设置一个密码

--.备份数据库
BACKUP DATABASE demo TO DISK = 'c:\demo.bak' with password='123456'

--.恢复数据库
RESTORE DATABASE demo FROM DISK = 'c:\demo.bak' with password='123456'

• 此时，在另外一台机器（若不指定密码）上执行 RESTORE DATABASE 恢复数据库，则会报错：

--.恢复数据库
RESTORE DATABASE demo FROM DISK = 'c:\demo.bak'

--.报错如下
消息 3279，级别 16，状态 2，第 1 行
由于密码错误，访问遭拒绝
消息 3013，级别 16，状态 1，第 1 行
RESTORE DATABASE 正在异常终止。

• 但从 SQL Server 2012 开始，WITH PASSWORD 这个选项被移除，不能再用于创建新的备份，否则会报错：

注：在 SQL Server 2008 上通过 BACKUP ... WITH PASSWORD 备份的文件，可以在 SQL Server 2019 上通过 RESTORE ... WITH PASSWORD 将其恢复，此时不会报错。

--.备份数据库
BACKUP DATABASE demo TO DISK = 'C:\demo.bak' with password='123456'

--.报错如下
消息 3032，级别 16，状态 2，第 1 行
此语句不支持一个或多个选项(password)。请查阅文档以了解所支持的选项。