# 1. 准备测试服务器
# 配置测试服务器
# 检查服务器状态
Get-Service | Where-Object { $_.Name -like ‘MSSQL*’ } | Select-Object Name, Status

Name Status
—- ——
MSSQLSERVER Running
SQLSERVERAGENT Running

# 2. 配置网络
# 配置测试网络
# 检查网络连接
Test-Connection -ComputerName fgedu-dr-server -Count 4

Source Destination IPV4Address IPV6Address
—— ———– ———– ———–
fgedu-prod fgedu-dr-server 192.168.1.200 ::1
fgedu-prod fgedu-dr-server 192.168.1.200 ::1
fgedu-prod fgedu-dr-server 192.168.1.200 ::1
fgedu-prod fgedu-dr-server 192.168.1.200 ::1

# 3. 复制生产数据
# 备份生产数据库
BACKUP DATABASE fgedudb TO DISK = ‘D:\\SQLServer\\Backup\\fgedudb_full.bak’ WITH COMPRESSION;
GO

# 复制备份文件到测试环境
Copy-Item -Path “D:\\SQLServer\\Backup\\fgedudb_full.bak” -Destination “\\fgedu-dr-server\\Backup\\”

# 4. 恢复数据库到测试环境
# 在测试环境恢复数据库
RESTORE DATABASE fgedudb FROM DISK = ‘D:\\SQLServer\\Backup\\fgedudb_full.bak’ WITH REPLACE,
MOVE ‘fgedudb’ TO ‘D:\\SQLServer\\Data\\fgedudb.mdf’,
MOVE ‘fgedudb_log’ TO ‘D:\\SQLServer\\Log\\fgedudb_log.ldf’;
GO

# 5. 配置测试环境
# 配置SQLServer参数
EXEC sp_configure ‘max server memory (MB)’, 65536;
RECONFIGURE;
GO

# 配置高可用
EXEC sp_configure ‘hadr enabled’, 1;
RECONFIGURE;
GO

# 6. 验证测试环境
# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases
WHERE name = ‘fgedudb’;
GO

name state_desc recovery_model_desc
———- ———- ——————-
fgedudb ONLINE FULL

# 检查高可用状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

name replica_server_name role_desc
———- ——————- ———
fgedu-ag fgedu-prod PRIMARY
fgedu-ag fgedu-dr-server SECONDARY

# 1. 演练启动
# 召开演练启动会议
# 确认演练准备情况
# 分配演练角色和职责

# 2. 灾难场景模拟
# 模拟主服务器故障
# 模拟存储故障
# 模拟网络故障
# 模拟数据损坏

# 3. 灾难响应
# 启动应急响应流程
# 通知相关人员
# 评估灾难影响
# 制定恢复策略

# 4. 灾难恢复
# 执行故障转移
# 恢复数据库
# 验证数据一致性
# 恢复应用服务

# 5. 服务验证
# 验证数据库服务
# 验证应用服务
# 验证数据完整性
# 验证业务功能

# 6. 演练总结
# 召开演练总结会议
# 评估演练结果
# 分析演练中发现的问题
# 提出改进建议

# 7. 环境恢复
# 恢复生产环境
# 清理测试环境
# 更新灾备方案和文档

# 演练流程时间安排

阶段 时间 主要任务 负责人
————————————————————
启动会议 09:00-09:30 确认准备情况，分配职责 演练协调员
场景模拟 09:30-10:00 模拟灾难场景 技术实施人员
灾难响应 10:00-10:30 启动应急响应 应急响应团队
灾难恢复 10:30-12:00 执行恢复操作 DBA团队
服务验证 12:00-13:00 验证服务和数据 业务人员
总结会议 13:00-13:30 评估结果，提出建议 演练协调员
环境恢复 13:30-14:00 清理测试环境 技术实施人员

# 1. 演练准备
# 检查主服务器状态
SELECT
@@SERVERNAME AS server_name,
SERVERPROPERTY(‘ProductVersion’) AS version;
GO

server_name version
—————– ———-
fgedu-prod 15.0.4198.2

# 检查可用性组状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

name replica_server_name role_desc synchronization_health_desc
———- ——————- ———– —————————
fgedu-ag fgedu-prod PRIMARY HEALTHY
fgedu-ag fgedu-dr-server SECONDARY HEALTHY

# 2. 模拟主服务器故障
# 停止主服务器SQLServer服务
Stop-Service -Name “MSSQLSERVER” -Force

# 检查主服务器状态
Get-Service -Name “MSSQLSERVER” | Select-Object Name, Status

Name Status
—- ——
MSSQLSERVER Stopped

# 3. 执行故障转移
# 在辅助服务器上执行故障转移
ALTER AVAILABILITY GROUP [fgedu-ag] FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

# 检查故障转移结果
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

name replica_server_name role_desc synchronization_health_desc
———- ——————- ———– —————————
fgedu-ag fgedu-prod SECONDARY NOT_HEALTHY
fgedu-ag fgedu-dr-server PRIMARY HEALTHY

# 4. 验证服务
# 检查新主服务器状态
SELECT
@@SERVERNAME AS server_name,
SERVERPROPERTY(‘ProductVersion’) AS version;
GO

server_name version
—————– ———-
fgedu-dr-server 15.0.4198.2

# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases
WHERE name = ‘fgedudb’;
GO

name state_desc recovery_model_desc
———- ———- ——————-
fgedudb ONLINE FULL

# 5. 恢复主服务器
# 启动原主服务器SQLServer服务
Start-Service -Name “MSSQLSERVER”

# 检查主服务器状态
Get-Service -Name “MSSQLSERVER” | Select-Object Name, Status

Name Status
—- ——
MSSQLSERVER Running

# 6. 回切操作
# 执行回切操作
ALTER AVAILABILITY GROUP [fgedu-ag] FAILOVER;
GO

# 检查回切结果
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

name replica_server_name role_desc synchronization_health_desc
———- ——————- ———– —————————
fgedu-ag fgedu-prod PRIMARY HEALTHY
fgedu-ag fgedu-dr-server SECONDARY HEALTHY

# 7. 演练总结
# 记录演练过程和结果
# 分析演练中发现的问题
# 提出改进建议

# 1. 演练准备
# 检查备份状态
SELECT
database_name,
type,
backup_finish_date,
backup_size
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’
ORDER BY backup_finish_date DESC
TOP 10;
GO

# 2. 模拟数据库损坏
# 创建测试表
CREATE TABLE dbo.fgedu_test (
id INT PRIMARY KEY,
name NVARCHAR(50)
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_test VALUES (1, ‘Test1’), (2, ‘Test2’), (3, ‘Test3’);
GO

# 模拟数据损坏
UPDATE dbo.fgedu_test SET name = ‘Corrupted’ WHERE id = 1;
GO

# 3. 执行恢复操作
# 检查最近的完整备份
DECLARE @last_full_backup DATETIME;
SELECT @last_full_backup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’ AND type = ‘D’;

# 检查最近的差异备份
DECLARE @last_diff_backup DATETIME;
SELECT @last_diff_backup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’ AND type = ‘I’ AND backup_finish_date > @last_full_backup;

# 检查最近的日志备份
DECLARE @last_log_backup DATETIME;
SELECT @last_log_backup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’ AND type = ‘L’ AND backup_finish_date > ISNULL(@last_diff_backup, @last_full_backup);

# 执行恢复操作
— 恢复完整备份
RESTORE DATABASE fgedudb FROM DISK = ‘D:\\SQLServer\\Backup\\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 恢复差异备份（如果有）
IF @last_diff_backup IS NOT NULL
BEGIN
RESTORE DATABASE fgedudb FROM DISK = ‘D:\\SQLServer\\Backup\\fgedudb_diff.bak’ WITH NORECOVERY;
END
GO

— 恢复日志备份（如果有）
IF @last_log_backup IS NOT NULL
BEGIN
RESTORE LOG fgedudb FROM DISK = ‘D:\\SQLServer\\Backup\\fgedudb_log.trn’ WITH RECOVERY;
END
ELSE
BEGIN
RESTORE DATABASE fgedudb WITH RECOVERY;
END
GO

# 4. 验证恢复结果
# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases
WHERE name = ‘fgedudb’;
GO

# 验证数据完整性
SELECT * FROM dbo.fgedu_test;
GO

id name
———– ———-
1 Test1
2 Test2
3 Test3

# 5. 演练总结
# 记录演练过程和结果
# 分析演练中发现的问题
# 提出改进建议

# 1. 服务验证
# 检查SQLServer服务状态
Get-Service | Where-Object { $_.Name -like ‘MSSQL*’ } | Select-Object Name, Status

# 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’);

# 检查可用性组状态
SELECT
ag.name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

# 2. 数据验证
# 检查数据完整性
DBCC CHECKDB(‘fgedudb’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

# 验证关键数据
SELECT TOP 10 * FROM dbo.fgedu_users;
SELECT TOP 10 * FROM dbo.fgedu_orders;

# 验证数据一致性
— 检查数据计数
SELECT COUNT(*) AS user_count FROM dbo.fgedu_users;
SELECT COUNT(*) AS order_count FROM dbo.fgedu_orders;

— 检查数据关系
SELECT
u.id,
u.username,
COUNT(o.id) AS order_count
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
GROUP BY u.id, u.username
TOP 10;

# 3. 性能验证
# 检查系统性能
SELECT
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE ‘%SQLServer:Buffer Manager%’
OR object_name LIKE ‘%SQLServer:General Statistics%’;

# 测试查询性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.id BETWEEN 1 AND 1000
GROUP BY u.id, u.username
ORDER BY order_count DESC;

DECLARE @end_time DATETIME = GETDATE();
SELECT DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;

# 4. 功能验证
# 测试数据插入
INSERT INTO dbo.fgedu_test VALUES (4, ‘Test4’);
SELECT * FROM dbo.fgedu_test WHERE id = 4;

# 测试数据更新
UPDATE dbo.fgedu_test SET name = ‘Updated’ WHERE id = 4;
SELECT * FROM dbo.fgedu_test WHERE id = 4;

# 测试数据删除
DELETE FROM dbo.fgedu_test WHERE id = 4;
SELECT * FROM dbo.fgedu_test WHERE id = 4;

# 5. 应用验证
# 测试应用连接
# 测试应用功能
# 测试业务流程

# 6. 文档验证
# 检查灾备方案文档
# 检查恢复流程文档
# 检查演练记录文档

# 验证结果评估

验证项 预期结果 实际结果 状态
————————————————————
服务状态 所有服务正常运行 所有服务正常运行 通过
数据库状态 所有数据库在线 所有数据库在线 通过
数据完整性 数据完整无损坏 数据完整无损坏 通过
数据一致性 数据关系正确 数据关系正确 通过
性能表现 性能符合要求 性能符合要求 通过
功能测试 所有功能正常 所有功能正常 通过
应用验证 应用正常运行 应用正常运行 通过
文档完整性 文档完整准确 文档完整准确 通过