部署 sqlserver 事务日志传送

关于资源规划：

部署过程

• 第1步，共享服务器：创建共享目录，主库执行 LSBackup_xxx 作业会将数据库日志备份到此目录

mkdir c:\opt\share
net user logshipping /del
net user logshipping mgLg0Z_JHaqTwyf /add /Y /expires:never

net share logshipping /del
net share logshipping=c:\opt\share /grant:administrator,full /grant:logshipping,full /grant:everyone,change

• 第2步，共享服务器：如果启用了防火墙，则建议将共享相关的端口开放给数据库服务器的内网地址，参考如下：

netsh advfirewall firewall add rule name="permit_share_tcp_135" protocol=TCP dir=in remoteip=192.168.0.0/255.255.0.0 localport=135 action=allow
netsh advfirewall firewall add rule name="permit_share_tcp_139" protocol=TCP dir=in remoteip=192.168.0.0/255.255.0.0 localport=139 action=allow
netsh advfirewall firewall add rule name="permit_share_tcp_445" protocol=TCP dir=in remoteip=192.168.0.0/255.255.0.0 localport=445 action=allow

• 第3步，主库和从库：验证共享目录是否可以访问（必须使用账号密码测试），否则执行主库 LSBackup_xxx 或从库 LSCopy_xxx 作业会报错拒绝访问

-- 启用 xp_cdmshell
exec sp_configure 'show advanced options', 1
reconfigure with override
go
exec sp_configure 'xp_cmdshell', 1
go
exec sp_configure 'show advanced options', 0
reconfigure with override
go

-- 测试一下
exec master..xp_cmdshell 'net use \\192.168.31.103\logshipping "mgLg0Z_JHaqTwyf" /user:192.168.31.103\logshipping'

• 第4步，主库：创建测试数据库，并进行一次完整备份，并保存到共享目录

create database abc_2025
backup database abc_2025 to disk = '\\192.168.31.103\logshipping\abc_2025.full.bak' with compression,stats=1

• 第5步，从库：进行一次完整恢复（with norecovery），从共享目录获取备份文件

restore database abc_2025 from disk = '\\192.168.31.103\logshipping\abc_2025.full.bak' with norecovery,replace,stats=1

• 第6步，从库：创建备份目录，执行 LSCopy_xxx 作业会从共享目录拷贝日志备份文件至此目录

注：如果遇到权限问题，则建议将该目录的读写权限授予agent代理帐户

mkdir c:\opt\logshiping

• 第7步，主库：选择要启用logshipping的数据库，右键 - 任务 - 事务日志传送 - 备份设置

👉 复制文件夹的网络路径：\\192.168.31.103\logshipping
👉 如果备份文件夹位于主服务器上：不用填写
删除文件，如果其保留时间超过：72 小时
在以下时间内没有执行备份时报警：1 小时
备份作业：每天在 0:00:00 和 23:59:59 之间、每 15 分钟执行

• 第8步，主库：事务日志传送 - 辅助数据库 - 添加辅助节点，示例 win102

👉 初始化辅助数据库：选择“否，辅助数据库已初始化”
👉 复制文件：复制文件的目标文件夹，选择从库的 c:\opt\logshiping
👉 还原事务日志：选择备用模式，👉 不要勾选 在还原备份时断开数据库中用户的连接
还原作业：每天在 0:00:00 和 23:59:59 之间、每 15 分钟执行

• 第9步，在主数据库创建一张测试表，然后按照顺序执行相关任务，验证数据是否已同步

-- 主库创建测试表
USE abc_2025
GO
CREATE TABLE tab01(id int)

-- 按照顺序执行相关任务
1.主库执行  LSBackup_xxx    备份日志到共享目录
2.从库执行  LSCopy_xxx      从共享目录copy最新的备份文件到本地目录
3.从库执行  LSRestore_xxx   从备份目录持续恢复日志

注：执行相关任务之前，辅助实例的db状态为 (正在还原...)，执行任务之后则变为 (备用 / 只读)

注意事项

关于权限的设定：

• 如果是域环境，则建议使用域账号启动sqlserver服务，然后共享目录开放给该域账号
• 如果不是域环境，则建议共享目录开放给 everyone 读写，然后配置防火墙，只开放给特定ip

关于日志传送与数据库备份计划的兼容性：

• 正常情况下，配置log shipping必须基于一次完整备份，后续追加的日志必须连续
• 完整备份和差异备份不会破坏log chain（日志链），所以额外执行完整备份、差异备份不会导致日志传送作业失败
• 而额外执行日志备份则会导致log shipping作业失败，所以对部署了日志传送任务的db，切勿配置日志备份计划