SQL Server灾备实验
1 概念
1.1 数据库备份
从物理与逻辑的角度，备份可分为：
1.1.1 物理备份
物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份方式，方法有：
l 冷备份（脱机备份）：是在关闭数据库的时候进行的；
l 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件；
l 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。
1.1.2 逻辑备份
逻辑备份是对数据库逻辑组件（如：表等数据库对象）的备份方式，从数据库的备份策略角度，可分为：
l 完全备份：每次对数据库进行完整的备份；
l 差异备份：备份自从上次完全备份之后被修改过的文件；
l 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。
1.2 完全备份、增量备份概述与对比
1.2.1 完全备份
完全备份是对整个数据库、数据库结构和文件结构的备份；保存的是备份完成时刻的数据库；是差异备份与增量备份的基础；每次对数据进行完整的备份。
该备份方式的优点为：
l 备份与恢复操作简单方便。
该备份方式的缺点为：
l 数据存在大量的重复；
l 占用大量的备份空间；
l 备份与恢复时间长。
1.2.2 增量备份
MySQL增量备份是自上一次备份后增加/变化的文件或者内容，其特点为：
l 没有重复数据，备份量不大，时间短；
l 依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低。
1.3 数据库镜像概念
1.3.1 数据库镜像概念
数据库管理系统自动把数据库或者其中的关键数据复制到另一个磁盘上；数据库系统自动保证镜像数据和主数据的一致性，每当主数据更新时，数据库管理系统自动把更新后的数据复制过去。
1.3.2 数据库镜像的用途
当系统出现介质故障时：
l 可由镜像磁盘继续提供使用；
l 同时数据库管理系统自动利用镜像磁盘数据进行数据的恢复；
l 不需要关闭系统和重装数据库副本。
当系统没出现介质故障时：
l 可用于并发操作；
l 一个用户加排它锁修改数据，其他用户可以读镜像数据库上的数据而不必等待该用户释放锁。
实际操作中，频繁的复制数据自然会降低系统的运行效率，在实际应用中用户往往只选择对关键数据和日志文件镜像，而不是对整个数据库镜像。
1.4 数据库镜像的优势
数据库镜像技术有以下优势：
l 消除存储方面的单一故障点：不用共享磁盘；
l 提高数据库可用性：快速自动/手动主从切换；
l 增强的数据保护：提供完整的数据冗余；
l 自动页修复：2008企业版在某些类型的错误导致页损坏，使其无法读取后，在 SQL Server 2008 企业版或更高版本上运行的数据库镜像伙伴（主体或镜像）将尝试自动修复该页。无法读取该页的伙伴将从其伙伴请求该页的新副本。如果此请求成功，则将以可读副本替换不可读的页，并且这通常会解决该错误。
1.5 数据库镜像的不足
镜像数据库技术有以下不足之处：
（1）版本限制：对于标准版的 SQL Server 实例，只可以使用“高安全模式”，即主体数据库与镜像数据库必须实现同步操作。在这种运行模式时，如果任何一个伙伴遇到性能问题，都将使同步操作带来较大的延时。通常标准版的 SQL Server 受到一些技术限制导致性能不能提升，从而使同步操作的延时更加明显。
（2）镜像数据库的访问限制：镜像数据库甚至不可以提供只读访问，只有通过创建快照才能访问，因此镜像数据库的利用率不高。
由于数据库镜像技术存在上述不足，SQL Server 后续产品将删除此项功能，建议改用 AlwaysOn 可用性组。SQL Server 2012 中已经引入了”AlwaysOn 基本可用性组”，用来替代数据库镜像技术。
2 数据库完整/差异备份、数据库恢复
2.1 实验环境
操作系统：Windows10
数据库：SQL Server
2.2 数据库完整/差异备份
2.2.1 创建数据库
创建一个“灾备demo”数据库，并创建stu表存储组员信息。如下所示。
CREATE TABLE stu (
StudentID int,
Class int,
Name nvarchar(255)
);
INSERT INTO stu (StudentID, Name)
VALUES
(2021212053,2021211803, N’张原赫’),
(2021212024,2021211803, N’范思宇’),
(2021212065,2021211803, N’方正阳’);
2.2.2 完整备份
右击需要备份的数据库，“任务”→“备份”。备份类型选择“完整”，删除自动生成的备份文件路径及文件名，填写备份路径及备份文件名。确认后，完成备份。如下所示。
2.2.3 差异备份
首先，查看数据表条目，查询一共有多少条信息，完整备份时的数据总数为3条。如下所示。
USE 灾备demo
select COUNT(1) ‘total’ from stu
接下来，新增几条记录，用作差异备份。添加之后，一共有11条。
进行差异备份，右击需要备份的数据库，“任务”→“备份”。备份类型：差异，填写备份路径及备份文件名。
2.3 数据库恢复
2.3.1 完整备份恢复
设置访问权限。
右击“数据库”→“还原数据库”，使用设备还原，选择完整备份文件，直接在目标数据库处更改还原数据库名。
成功恢复。如下所示。
2.3.2 差异备份还原
使用脚本进行差异备份，还原到和完整备份相同的目标数据库。
查询总条目进行验证，成功恢复。如下所示。
3 同步或异步镜像搭建、数据实时复制
3.1 实验环境
服务器1：系统Windows10，IP地址192.168.75.161
服务器2：系统Windows10，IP地址192.168.75.162
3.2 实验准备
3.2.1 新建用户名
统一在主服务器和从服务器上新建一个登录名：DR_user，同时赋予 sysadmin角色。如下所示。
3.2.2 数据库准备
在主服务器数据库中新建数据库“DR_exp”，其中stu 表记录小组成员信息如下所示。
3.3 同步或异步镜像搭建
3.3.1 创建master数据库主密钥
分别在两台虚拟机上执行如下命令：
Use master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘23987hxJ#KL95234nl0zBe’;
GO
3.3.2 对服务器实例创建一个用于其数据库镜像出站连接的加密证书
在主服务器上执行如下命令：
USE master;
CREATE CERTIFICATE SQLSVR1_cert WITH SUBJECT =’SQLSVR1 certificate for database
mirroring’,
start_date=’2024-03-01’,expiry_date=’2099-01-01’;
GO
在从服务器上执行如下命令：
USE master;
go
CREATE CERTIFICATE SQLSVR2_cert WITH SUBJECT =’SQLSVR2 certificate for database mirroring’,
start_date=’2024-03-01’,expiry_date=’2099-01-01’;
GO
这里设置了证书的有效期是从 2024 年到 2099 年。如下所示。
3.3.3 使用服务器实例证书为服务器创建端点
使用主服务器实例的证书SQLSVR1_cert为主服务器SQLSVR1创建端点，在主服务器上执行如下命令：
Use master;
go
CREATE ENDPOINT DbMirroringEndpoint
STATE = STARTED AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLSVR1_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
使用从服务器实例的证书SQLSVR2_cert为从服务器SQLSVR2创建端点，在从服务器上执行如下命令：
Use master;
go
CREATE ENDPOINT DbMirroringEndpoint
STATE = STARTED AS TCP(
LISTENER_PORT=5022,
LISTENER_IP =ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLSVR2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE =ALL
);
GO
在上述命令中：
CREATE ENDPOINT DbMirroringEndpoint：这条命令是用来创建一个名为 DbMirroringEndpoint 的端点。端点是 SQL Server 上的一个对象，用于定义一个通信端口，允许 SQL Server 实例之间或与客户端之间进行网络通信；
AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL )：此部分配置端点使用 TCP 协议进行通信，并监听所有网络接口上的 5022 端口；
FOR DATABASE_MIRRORING：指定端点的用途为数据库镜像，这意味着这个端点将被用于数据库镜像会话中，以支持数据库之间的数据同步和故障转移。
3.3.4 备份服务器的加密证书
在主服务器上执行如下命令：
BACKUP CERTIFICATE SQLSVR1_cert TO FILE =’C:\data\SQLSVR1.cer’;
在从服务器上执行如下命令：
BACKUP CERTIFICATE SQLSVR2_cert TO FILE =’C:\data\SQLSVR2.cer’;
将证书存在了各自 C:\data\ 目录下。如下所示。
3.3.5 分别为另一个服务器创建登录名和用户
在主服务器实例的 master 数据库中为镜像服务器创建一个登录名，同时为该用户名创建一个用户。
USE master;
go
CREATE LOGIN SQLSVR2_login WITH PASSWORD=‘Sample@#’;
CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login;
GO
在从服务器实例的 master 数据库中为主服务器创建一个登录名，同时为该用户名创建一个用户。
USE master;
go
CREATE LOGIN SQLSVR1_login WITH PASSWORD=‘Sample@#’;
CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login;
GO
3.3.6 将上述创建的用户与对应服务器的证书相关联
这里首先要将各自服务器生成的证书复制粘贴到对方相应目录下。
在主服务器中执行如下命令：
Use master;
go
CREATE CERTIFICATE SQLSVR2_cert AUTHORIZATION SQLSVR2_user FROM FILE =
‘C:\data\SQLSVR2.cer’
GO
在从服务器中执行如下命令：
Use master;
go
CREATE CERTIFICATE SQLSVR1_cert AUTHORIZATION SQLSVR1_user FROM FILE =
‘C:\data\SQLSVR1.cer’
GO
3.3.7 授予登录名对数据库镜像端点的 CONNECT 权限
在主服务器中执行如下命令：
GRANT connect on endpoint::DbMirroringEndpoint TO SQLSVR2_login;
GO
在从服务器中执行如下命令：
GRANT connect on endpoint::DbMirroringEndpoint TO SQLSVR1_login;
GO
3.3.8 主服务器备份数据库，从服务器恢复数据库
设置主服务器中主库为完整恢复模式，并备份到对应目录。在主服务器中执行如下命令：
Alter database DR_exp set recovery full with no_wait
Backup database DR_exp to disk=’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLSERVER\MSSQL\Backup\DR_exp.bak’
执行成功。如下所示。
在从服务器中还原主数据库的备份文件（先手动把备份文件拷贝到从服务器上）。在从服务器中执行如下命令：
Restore database DR_exp from disk=’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLSERVER\MSSQL\Backup\DR_exp.bak’ with norecovery
3.3.9 设置镜像
执行下述操作，192.168.75.161 为主服务器的 IP 地址。
Alter database DR_exp set partner=’TCP://192.168.75.161:5022’
在从服务器上操作完成后，在主服务器上进行如下操作，192.168.75.162 为主从服务器的 IP 地址。
Alter database DR_exp set partner=’TCP://192.168.75.162:5022’
3.3.10 配置完成
至此，镜像配置完成。查看主服务器上数据库状态，显示如下：
从服务器上显示如下：
同时，可以使用 T-SQL 语句来检查镜像同步状态：
SELECT DB_NAME(database_id) AS DatabaseName, mirroring_state_desc
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
执行结果如下，SYNCHRONIZED 意味着数据已经实时复制并保持同步。
3.3.11 主备切换
在高安全模式下，在主机执行如下命令及完成主备切换：
use master;
alter database DR_exp set partner failover;
刷新之后可以看到主服务器和从服务器进行切换：
3.4 数据实时复制
3.4.1 在主服务器上对数据库进行增删改的操作
如下是在 DR_exp 数据库的 team_info 表中新插入一行数据：
3.4.2 检查镜像数据库的数据
使用数据库快照：在镜像服务器上创建数据库的快照，然后查询该快照中的数据。数据库快照提供了镜像数据库在创建快照时刻的静态视图。
在镜像服务器上创建快照：
CREATE DATABASE DR_exp_snapshot ON
(NAME = DR_exp, FILENAME = ‘C:\Program Files\Microsoft SQL
Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\DR_exp_snapshot.ss’)
AS SNAPSHOT OF DR_exp;
查询快照数据：
use DR_exp_snapshot;
select *from team_info;
4 创建快照、查询快照
4.1 实验环境
操作系统：Windows10
数据库：SQL Server
4.2 实验准备
使用“灾备demo”数据库，其中表“stu”中记录了小组三人的姓名学号。
4.3 创建快照
创建数据库“灾备demo”的快照，并命名为“Snapshot_Test_shot”，存储于 C:\test\test.mdf 。
刷新后发现数据库快照文件夹中已存在该快照。创建快照成功。
create database Snapshot_Test_shot ON
(
Name = 灾备demo,
FileName = ‘C:\test\test.mdf’
)
AS SNAPSHOT OF 灾备demo;
4.4 查询快照
查询快照中的“stu”表，查询成功。
USE Snapshot_Test_shot ;
SELECT * FROM stu
4.5 使用快照恢复数据库
删除数据库“灾备demo”中的“stu”表，刷新后发现删除成功。
执行命令，将“test”数据库恢复为数据库快照“Snapshot_Test_shot”。
use master;
GO
RESTORE DATABASE 灾备demo from
DATABASE_SNAPSHOT = ‘Snapshot_Test_shot’;
再次查询“灾备demo”数据库中的“stu”表,查询成功。数据库通过快照恢复成功。
5 总结
通过本次实验，我们了解到了数据库的多种备份方式的整体操作过程，即差异备份和完整备份，同时，掌握了在异常情况发生后数据库恢复的基本方法。之后，我们使用SQL Server完成了同步镜像搭建、数据实时复制，以及创建和查询快照的过程。这次实验让我们对SQL Server的高可用性和数据恢复功能有了更深入的了解，并提高了实际操作能力。
wechat- alipay
