# 灾难恢复配置与设置

# 1. 配置备份策略

# 完整备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH INIT, COMPRESSION;
GO

# 差异备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_diff.bak’ WITH DIFFERENTIAL, COMPRESSION;
GO

# 事务日志备份
BACKUP LOG fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_log.trn’ WITH COMPRESSION;
GO

# 2. 配置日志传送

# 在主服务器上执行
— 创建备份共享文件夹
— 确保SQLServer服务账户有访问权限

— 配置日志备份
EXEC sp_add_log_shipping_primary_database
@database = ‘fgedudb’,
@backup_directory = ‘\fgedu1\backup’,
@backup_share = ‘\\fgedu1\backup’,
@backup_job_name = ‘LSBackup_fgedudb’,
@backup_retention_period = 72,
@backup_compression = 1;
GO

# 在辅助服务器上执行
— 创建数据库（如果不存在）
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 配置日志传送辅助数据库
EXEC sp_add_log_shipping_secondary_primary
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@backup_source_directory = ‘\\fgedu1\backup’,
@backup_destination_directory = ‘\fgedu2\backup’,
@copy_job_name = ‘LSCopy_fgedu1_fgedudb’,
@restore_job_name = ‘LSRestore_fgedu1_fgedudb’,
@file_retention_period = 72;
GO

— 添加辅助数据库
EXEC sp_add_log_shipping_secondary_database
@secondary_database = ‘fgedudb’,
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@restore_delay = 0,
@restore_mode = 1, — 1 = NORECOVERY, 2 = STANDBY
@disconnect_users = 0,
@restore_threshold = 45,
@threshold_alert_enabled = 1,
@history_retention_period = 5760;
GO

# 3. 配置Always On可用性组（跨区域）

# 创建可用性组
CREATE AVAILABILITY GROUP [fgedu_dr_ag]
WITH (DB_FAILOVER = ON, AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR DATABASE [fgedudb]
REPLICA ON
N’FGEDU1′ WITH (
ENDPOINT_URL = N’TCP://fgedu1.fgedu.net.cn:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
N’FGEDU2′ WITH (
ENDPOINT_URL = N’TCP://fgedu2.fgedu.net.cn:5022′,
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 10,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO

# 配置可用性组监听器
ALTER AVAILABILITY GROUP [fgedu_dr_ag]
ADD LISTENER N’fgedu_dr_listener’ (
WITH IP ((N’192.168.1.100′, N’255.255.255.0′)),
PORT = 1433
);
GO

# 灾难恢复演练与测试

# 1. 备份恢复测试

# 步骤1：执行完整备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH INIT, COMPRESSION;
GO

# 步骤2：执行差异备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_diff.bak’ WITH DIFFERENTIAL, COMPRESSION;
GO

# 步骤3：执行事务日志备份
BACKUP LOG fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_log1.trn’ WITH COMPRESSION;
GO

# 步骤4：在测试环境中恢复数据库
— 恢复完整备份
RESTORE DATABASE fgedudb_test FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 恢复差异备份
RESTORE DATABASE fgedudb_test FROM DISK = ‘\fgedu1\backup\fgedudb_diff.bak’ WITH NORECOVERY;
GO

— 恢复事务日志备份
RESTORE LOG fgedudb_test FROM DISK = ‘\fgedu1\backup\fgedudb_log1.trn’ WITH RECOVERY;
GO

# 步骤5：验证恢复结果
SELECT COUNT(*) FROM fgedudb_test.dbo.fgedu_users;
GO

# 2. 日志传送演练

# 步骤1：检查日志传送状态
EXEC sp_help_log_shipping_monitor_primary @primary_server = ‘fgedu1’, @primary_database = ‘fgedudb’;
GO

# 步骤2：在辅助服务器上执行故障转移
— 应用所有未应用的事务日志
RESTORE LOG fgedudb WITH RECOVERY;
GO

# 步骤3：验证故障转移结果
SELECT @@SERVERNAME AS current_server;
GO

SELECT COUNT(*) FROM dbo.fgedu_users;
GO

# 步骤4：将辅助服务器设置回备用状态
— 在主服务器上执行完整备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH INIT, COMPRESSION;
GO

— 在辅助服务器上执行还原
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 重新配置日志传送
EXEC sp_add_log_shipping_secondary_database
@secondary_database = ‘fgedudb’,
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@restore_delay = 0,
@restore_mode = 1,
@disconnect_users = 0,
@restore_threshold = 45,
@threshold_alert_enabled = 1,
@history_retention_period = 5760;
GO

# 3. Always On可用性组演练

# 步骤1：检查可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.synchronization_health_desc AS synchronization_health
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

# 步骤2：执行手动故障转移
ALTER AVAILABILITY GROUP [fgedu_dr_ag] FAILOVER;
GO

# 步骤3：验证故障转移结果
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.synchronization_health_desc AS synchronization_health
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

# 步骤4：执行回切故障转移
ALTER AVAILABILITY GROUP [fgedu_dr_ag] FAILOVER;
GO

# 步骤5：验证回切结果
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.synchronization_health_desc AS synchronization_health
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

# 灾难恢复监控与维护

# 1. 监控备份状态

# 查看备份历史
SELECT
database_name,
backup_type = CASE type
WHEN ‘D’ THEN ‘完整备份’
WHEN ‘I’ THEN ‘差异备份’
WHEN ‘L’ THEN ‘事务日志备份’
WHEN ‘F’ THEN ‘文件备份’
WHEN ‘G’ THEN ‘文件组备份’
WHEN ‘P’ THEN ‘部分备份’
ELSE ‘其他’
END,
backup_start_date,
backup_finish_date,
backup_size / 1024 / 1024 AS backup_size_mb,
physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = ‘fgedudb’
ORDER BY bs.backup_start_date DESC;
GO

# 2. 监控日志传送状态

# 查看主服务器日志传送状态
EXEC sp_help_log_shipping_monitor_primary @primary_server = ‘fgedu1’, @primary_database = ‘fgedudb’;
GO

# 查看辅助服务器日志传送状态
EXEC sp_help_log_shipping_monitor_secondary @secondary_server = ‘fgedu2’, @secondary_database = ‘fgedudb’;
GO

# 3. 监控Always On可用性组状态

# 查看可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name AS replica,
ars.role_desc AS role,
ars.operational_state_desc AS operational_state,
ars.synchronization_health_desc AS synchronization_health
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

# 查看数据库同步状态
SELECT
db_name(drs.database_id) AS database_name,
drs.replica_server_name AS replica,
drs.synchronization_state_desc AS synchronization_state,
drs.synchronization_health_desc AS synchronization_health,
drs.last_hardened_lsn,
drs.last_redone_lsn,
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs
ORDER BY db_name(drs.database_id), drs.replica_server_name;
GO

# 4. 创建灾难恢复监控表
CREATE TABLE dbo.fgedu_dr_monitor (
monitor_id INT PRIMARY KEY IDENTITY,
monitor_time DATETIME NOT NULL DEFAULT GETDATE(),
dr_type NVARCHAR(50) NOT NULL, — 备份、日志传送、Always On
status NVARCHAR(50) NOT NULL,
details NVARCHAR(MAX) NULL,
recovery_point DATETIME NULL,
recovery_time INT NULL — 恢复时间（秒）
);
GO

# 5. 创建灾难恢复监控存储过程
CREATE PROCEDURE dbo.usp_monitor_dr
AS
BEGIN
SET NOCOUNT ON;

— 监控备份状态
INSERT INTO dbo.fgedu_dr_monitor (
dr_type,
status,
details,
recovery_point
)
SELECT
‘备份’,
CASE WHEN MAX(backup_finish_date) >= DATEADD(day, -1, GETDATE()) THEN ‘正常’ ELSE ‘异常’ END,
‘最近备份：’ + CONVERT(VARCHAR(20), MAX(backup_finish_date), 120),
MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’;

— 监控日志传送状态
INSERT INTO dbo.fgedu_dr_monitor (
dr_type,
status,
details
)
SELECT
‘日志传送’,
CASE WHEN last_restored_date >= DATEADD(hour, -1, GETDATE()) THEN ‘正常’ ELSE ‘异常’ END,
‘最近恢复：’ + CONVERT(VARCHAR(20), last_restored_date, 120)
FROM msdb.dbo.log_shipping_monitor_secondary
WHERE secondary_database = ‘fgedudb’;

— 监控Always On可用性组状态
INSERT INTO dbo.fgedu_dr_monitor (
dr_type,
status,
details
)
SELECT
‘Always On’,
CASE WHEN synchronization_health_desc = ‘HEALTHY’ THEN ‘正常’ ELSE ‘异常’ END,
‘同步状态：’ + synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states
WHERE replica_id IN (
SELECT replica_id FROM sys.availability_replicas WHERE replica_server_name = @@SERVERNAME
);
END;
GO

# 6. 执行灾难恢复监控
EXEC dbo.usp_monitor_dr;
GO

# 7. 查看监控结果
SELECT * FROM dbo.fgedu_dr_monitor ORDER BY monitor_time DESC;
GO

# 8. 维护灾难恢复解决方案

# 定期清理备份文件
EXEC xp_delete_file 0, ‘\fgedu1\backup’, ‘bak’, DATEADD(day, -7, GETDATE()), 1;
EXEC xp_delete_file 0, ‘\fgedu1\backup’, ‘trn’, DATEADD(day, -7, GETDATE()), 1;
GO

# 定期更新统计信息
UPDATE STATISTICS dbo.fgedu_dr_monitor;
GO

# 定期测试备份恢复
— 每月执行一次备份恢复测试

# 灾难恢复配置实战案例

# 1. 环境准备
# 主服务器：fgedu1.fgedu.net.cn
# 恢复服务器：fgedu2.fgedu.net.cn
# 备份存储：\fgedu1\backup

# 2. 配置备份策略

# 创建备份作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Backup fgedudb’,
@enabled = 1,
@description = N’备份fgedudb数据库’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Backup fgedudb’,
@step_name = N’完整备份’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb TO DISK = ”\fgedu1\backup\fgedudb_full.bak” WITH INIT, COMPRESSION;’,
@freq_type = 4, — 每天
@freq_interval = 1,
@freq_subday_type = 1, — 一次
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20231201,
@active_end_date = 99991231,
@active_start_time = 000000,
@active_end_time = 235959;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Backup fgedudb’,
@step_name = N’差异备份’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb TO DISK = ”\fgedu1\backup\fgedudb_diff.bak” WITH DIFFERENTIAL, COMPRESSION;’,
@freq_type = 4, — 每天
@freq_interval = 1,
@freq_subday_type = 4, — 每小时
@freq_subday_interval = 4,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20231201,
@active_end_date = 99991231,
@active_start_time = 000000,
@active_end_time = 235959;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Backup fgedudb’,
@step_name = N’事务日志备份’,
@subsystem = N’TSQL’,
@command = N’BACKUP LOG fgedudb TO DISK = ”\fgedu1\backup\fgedudb_log.trn” WITH COMPRESSION;’,
@freq_type = 4, — 每天
@freq_interval = 1,
@freq_subday_type = 4, — 每小时
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20231201,
@active_end_date = 99991231,
@active_start_time = 000000,
@active_end_time = 235959;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Backup fgedudb’,
@server_name = N'(local)’;
GO

# 3. 配置日志传送

# 在主服务器上执行
EXEC sp_add_log_shipping_primary_database
@database = ‘fgedudb’,
@backup_directory = ‘\fgedu1\backup’,
@backup_share = ‘\\fgedu1\backup’,
@backup_job_name = ‘LSBackup_fgedudb’,
@backup_retention_period = 72,
@backup_compression = 1;
GO

# 在辅助服务器上执行
— 创建数据库
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 配置日志传送辅助数据库
EXEC sp_add_log_shipping_secondary_primary
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@backup_source_directory = ‘\\fgedu1\backup’,
@backup_destination_directory = ‘\fgedu2\backup’,
@copy_job_name = ‘LSCopy_fgedu1_fgedudb’,
@restore_job_name = ‘LSRestore_fgedu1_fgedudb’,
@file_retention_period = 72;
GO

— 添加辅助数据库
EXEC sp_add_log_shipping_secondary_database
@secondary_database = ‘fgedudb’,
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@restore_delay = 0,
@restore_mode = 1,
@disconnect_users = 0,
@restore_threshold = 45,
@threshold_alert_enabled = 1,
@history_retention_period = 5760;
GO

# 4. 验证配置

# 查看备份作业状态
SELECT
job.name AS job_name,
CASE WHEN job.enabled = 1 THEN ‘启用’ ELSE ‘禁用’ END AS status,
hist.run_status,
hist.run_date,
hist.run_time,
hist.message
FROM msdb.dbo.sysjobs job
JOIN msdb.dbo.sysjobhistory hist ON job.job_id = hist.job_id
WHERE job.name LIKE ‘%Backup%’ OR job.name LIKE ‘%LS%’
ORDER BY hist.run_date DESC, hist.run_time DESC;
GO

# 查看日志传送状态
EXEC sp_help_log_shipping_monitor_primary @primary_server = ‘fgedu1’, @primary_database = ‘fgedudb’;
GO

EXEC sp_help_log_shipping_monitor_secondary @secondary_server = ‘fgedu2’, @secondary_database = ‘fgedudb’;
GO

# 灾难恢复演练实战案例

# 1. 演练目标
# 测试在主服务器故障时，通过日志传送将辅助服务器提升为新的主服务器的能力

# 2. 演练步骤

# 步骤1：检查日志传送状态
EXEC sp_help_log_shipping_monitor_primary @primary_server = ‘fgedu1’, @primary_database = ‘fgedudb’;
GO

EXEC sp_help_log_shipping_monitor_secondary @secondary_server = ‘fgedu2’, @secondary_database = ‘fgedudb’;
GO

# 步骤2：在主服务器上执行最后的事务日志备份
BACKUP LOG fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_log_final.trn’ WITH COMPRESSION;
GO

# 步骤3：将最后的事务日志备份复制到辅助服务器
— 手动复制或通过复制作业

# 步骤4：在辅助服务器上应用最后的事务日志备份
RESTORE LOG fgedudb FROM DISK = ‘\fgedu2\backup\fgedudb_log_final.trn’ WITH RECOVERY;
GO

# 步骤5：验证辅助服务器上的数据库状态
SELECT @@SERVERNAME AS current_server;
GO

SELECT COUNT(*) FROM dbo.fgedu_users;
GO

# 步骤6：将辅助服务器配置为新的主服务器
— 更新应用连接字符串指向新的主服务器
— 重新配置备份策略

# 步骤7：清理演练环境
— 在原主服务器上重新配置数据库
— 重新配置日志传送

# 3. 演练结果

# 验证项：
# – 辅助服务器成功提升为新的主服务器
# – 数据库完整性验证通过
# – 应用能够连接到新的主服务器
# – 恢复时间符合RTO要求

# 演练报告：
/*
演练日期：2023-12-01
演练类型：日志传送故障转移演练
演练结果：成功
恢复时间：15分钟
数据损失：无
问题发现：无
建议：定期执行灾难恢复演练
*/

# 灾难恢复故障处理案例

# 1. 故障场景：备份失败

# 症状：
# – 备份作业失败
# – 错误日志显示备份失败原因

# 处理步骤：

# 步骤1：查看错误日志
EXEC xp_readerrorlog 0, 1, ‘BACKUP’;
GO

# 步骤2：检查备份目录权限
# 确保SQLServer服务账户有备份目录的写入权限

# 步骤3：检查磁盘空间
EXEC xp_fixeddrives;
GO

# 步骤4：修复备份问题
— 如果是权限问题，修改权限
— 如果是磁盘空间问题，清理磁盘空间
— 如果是数据库问题，修复数据库

# 步骤5：重新执行备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH INIT, COMPRESSION;
GO

# 2. 故障场景：日志传送同步失败

# 症状：
# – 日志传送复制作业失败
# – 辅助服务器上的数据库不同步

# 处理步骤：

# 步骤1：查看错误日志
EXEC xp_readerrorlog 0, 1, ‘Log Shipping’;
GO

# 步骤2：检查网络连接
# 测试主服务器和辅助服务器之间的网络连接

# 步骤3：检查共享文件夹权限
# 确保SQLServer服务账户有共享文件夹的访问权限

# 步骤4：检查事务日志备份
SELECT
database_name,
backup_start_date,
backup_finish_date,
physical_device_name
FROM msdb.dbo.backupset
WHERE database_name = ‘fgedudb’ AND type = ‘L’
ORDER BY backup_start_date DESC;
GO

# 步骤5：修复日志传送
— 如果是网络问题，修复网络连接
— 如果是权限问题，修改权限
— 如果是备份问题，重新配置备份

# 步骤6：重新初始化日志传送
— 在主服务器上执行完整备份
BACKUP DATABASE fgedudb TO DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH INIT, COMPRESSION;
GO

— 在辅助服务器上执行还原
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 重新配置日志传送
EXEC sp_add_log_shipping_secondary_database
@secondary_database = ‘fgedudb’,
@primary_server = ‘fgedu1’,
@primary_database = ‘fgedudb’,
@restore_delay = 0,
@restore_mode = 1,
@disconnect_users = 0,
@restore_threshold = 45,
@threshold_alert_enabled = 1,
@history_retention_period = 5760;
GO

# 3. 故障场景：Always On可用性组同步失败

# 症状：
# – 可用性组同步状态为NOT SYNCHRONIZING
# – 辅助节点无法同步主节点的数据

# 处理步骤：

# 步骤1：查看错误日志
EXEC xp_readerrorlog 0, 1, ‘Always On’;
GO

# 步骤2：检查网络连接
# 测试主节点和辅助节点之间的网络连接

# 步骤3：检查数据库状态
SELECT
db_name(drs.database_id) AS database_name,
drs.replica_server_name AS replica,
drs.synchronization_state_desc AS synchronization_state,
drs.synchronization_health_desc AS synchronization_health,
drs.error_number,
drs.error_message
FROM sys.dm_hadr_database_replica_states drs
ORDER BY db_name(drs.database_id), drs.replica_server_name;
GO

# 步骤4：修复同步问题
— 如果是网络问题，修复网络连接
— 如果是数据库问题，修复数据库
— 如果是日志积压问题，等待日志应用完成

# 步骤5：重新同步可用性组
— 从辅助节点移除数据库
ALTER DATABASE fgedudb SET HADR OFF;
GO

— 在辅助节点上还原数据库
RESTORE DATABASE fgedudb FROM DISK = ‘\fgedu1\backup\fgedudb_full.bak’ WITH NORECOVERY;
GO

— 将数据库重新加入可用性组
ALTER DATABASE fgedudb SET HADR AVAILABILITY GROUP = [fgedu_dr_ag];
GO