MSSQL数据库自动备份和自动复制转移备份
数据备份的重要性是老生常谈。但长期以来，企业组织对数据备份的认同度仅在于“不要丢了数据”。
笔者：国际认证信息系统审计师、软考系统分析师
但“不要丢数据”这个要求，潜台词就是不仅要有做数据备份，还应该有不止一份备份才行。
备份的手段有很多种，不差钱的甲方，可以购买各种专门的备份设备，比如国产的某数品牌备份系统，在笔者的工作环境中也还算是工作良好的。
但无论差不差钱，刚才说过了，备份手段、备份数量都不能只有一种或一套，尤其是备份必须要有副本。
因此，笔者就在自己的工作环境中，除了使用专用备份机进行备份之外，还通过自动化脚本去实现产生数据备份和转移数据备份的过程。
本文涉及的知识点包括：MS SQL Server数据库、PowerShell、NET和ROBOCOPY命令行程序、批处理、计划任务、Windows网络文件共享等。
作为例子的服务器环境包括一台MS SQL Server 数据库服务器和一台Windows Storage Server （WSS）存储服务器。也就是备份操作的目标是数据库服务器，数据备份的最终存储位置是在WSS存储服务器。
整个设置过程简要概括如下：
1、创建MS SQL 数据库维护计划，能在指定位置生成数据备份文件。
2、编写PowerShell脚本，实现从脚本启动MS SQL数据库备份，并把备份文件复制转移到WSS存储服务器。
3、通过Windows计划任务，实现定时运行PowerShell脚本。
首先我们需要通过Microsoft SQL Server Management Studio，连接到数据库服务器，创建数据库维护计划进行数据备份。相关的教程网络上很多，这里仅以设置截图说明一下，不详细介绍。
1、设置维护计划
2、设置维护计划属性
3、设置清除维护操作内容
以上维护计划设置完成后，需要进行执行测试，确认能在指定路径下生成数据库备份。按笔者经验，需要检查的细节包括：
（1）SQL Server Agent服务属性是否已经设置为自动运行。
（2）SQL Server Agent服务属性中的登录身份，建议修改选择为“本地系统账号”。
（3）做等保要三员分立、管理员账号改名、本地安全策略要进行各种限制性的调整，务必要确认这些调整是否会影响维护计划的运行，以及是否影响到维护计划的所有者是否有效。
（4）SQL Server代理的属性是否已经设置在意外停止时自动重新启动。
（5）维护计划使用的数据库连接是否有效。
（6）维护计划作业步骤属性所有的设置是否都正确。
由于本文重点不在于MS SQL SERVER备份，所以不作深入介绍。
1、在WSS存储服务器上创建名为BackupOp的用户，设置密码为Share4Password（以上用户名和密码仅为示例），设置用户属于Backup Operators用户组。在测试期间，可以同时设置用户属于Administrators组，测试完成后取消该归属。
2、在WSS存储服务器可用的盘符下建立根文件夹，用于共享和存放备份，比如名为D:\BACKUPDB。把该根文件夹设置为共享文件夹，设置仅允许BackupOp用户可以远程读写访问。测试期间也可添加其它用户，测试完成后删除仅保留BackupOp用户。
3、检查该根文件夹的本地安全权限设置，确保BackupOp用户可以完全读写和控制。
1、在数据库服务器上创建同样名为BackupOp的用户，设置和WSS存储服务器上的同名用户相同的密码Share4Password，以及同样设置归属用户组Backup Operators。同样地，在测试期间，可以同时设置用户属于Administrators组，测试完成后取消该归属。
2、设置数据库本地备份的目录能被BackupOp用户访问。最简单的做法就是设置为允许完全控制。如果要细分，读权限是必须的，写权限则取决如何删除备份。如果是通过ROBOCOPY复制后删除文件，则需要BackupOp用户可以写入和删除。如果是通过数据库维护计划实现自动删除旧备份，则读权限就够了。
3、在MS SQL Server中添加BackupOp用户到安全性设置。
设置相同的用户名和密码是为了在一定程度上避免Windows网络共享的一些问题，这里就不详细说了。
我们需要通过PowerShell脚本实现调用SQL Server维护任务执行数据备份。这里的重点在于需要等待数据备份完成后再进行数据备份的复制转移。但MS SQL Server系统默认提供的sp_start_job系统存储过程是异步操作，启动任务后就立即返回，不会等待操作完成。
因此，有人编写了名为sp_start_job_wait的存储过程去解决问题。存储过程代码篇幅有点长，这里不直接贴了，参见以下网址：
1、创建自定义存储过程。我们需要在msdb这个系统数据库的“可编程性-存储过程”这个位置下，创建上面说的sp_start_job_wait自定义存储过程，如图：
2、检查数据库服务器是否已经具备MS SQL Server的PowerShell扩展。对于当前较新版本的MS SQL Server，在安装时就已经同步安装了名为SQLPS的简化版PowerShell扩展，提供了必须的Invoke-Sqlcmd命令。
如果有其它扩展需要，可以安装完整版本的SqlServer PowerShell扩展。方法是通过管理员身份启动PowerShell或者PowerShell ISE，运行如下命令实现自动安装：
Install-Module -Name SqlServer
如果之前已经安装，可以通过如下命令更新：
Update-Module -Name SqlServer -AllowClobber
检查安装情况，会给出模块类型、版本、名称和引出的命令等信息：
Get-Module SqlServer -ListAvailable
如果需要卸载：
Uninstall-module -Name SQLServer -RequiredVersion "版本号"
无论安装的是简化版或完整版，都可以在数据库服务器上运行PowerShell，然后执行简单的命令来测试：
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "数据库实例名称或IP地址"
运行成功时将返回当前时间信息。
3、接下来看看脚本内容。脚本文件我们放置在C:\Users目录下，命名为AutoBackupTransfer.ps1：
脚本内容：
# 1 导入SQLSERVER POWERSHELL模块
Import-Module SQLPS
# 2 调用维护计划任务，产生备份
# 注意：运行本脚本的WINDOWS用户必须已经在SQL SERVER的安全性中登记，并能启动维护任务，服务器角色设置成sysadmin，也就是数据库管理员。
# 假设本地SQL SERVER数据库地址是192.168.100.1，也可以写数据库实例名
# 假设维护计划的任务名称是MaintenancePlan.Subplan_1 注意要有子计划名称
Invoke-Sqlcmd -ServerInstance "192.168.100.1" -Database MSDB -query "EXEC dbo.sp_start_job_wait N'MaintenancePlan.Subplan_1';"
# 3 数据库每日备份的脚本
# 假设存储服务器的文件共享为 \\192.168.100.2\BACKUPDB
# 假设访问存储服务器的文件共享用户名为 BackupOp，密码为 Share4Password
# 假设数据库备份的本地路径为 D:\MSSQLDB\MSSQL13.MSSQLSERVER\MSSQL\Backup
# 3.1 命令行连接文件共享
net use \\192.168.100.2\BACKUPDB Share4Password /user:BackupOp /persistent:no
# 3.2 复制转移数据库备份
robocopy D:\MSSQLDB\MSSQL13.MSSQLSERVER\MSSQL\Backup \\192.168.100.2\BACKUPDB *.* /R:0 /W:0 /S /B /J /MIR /V /NP
# 3.3 断开文件共享
net use \\192.168.100.2\BACKUPDB /delete /y
为确认脚本有效，我们可以通过管理员或者BackupOp用户登录服务器后，以管理员身份启动PowerShell或者PowerShell ISE，然后运行脚本，观察其执行过程和执行结果，以确认脚本有效。
以上脚本中，关键利用了Invoke-Sqlcmd命令，以及两个命令行程序：NET和ROBOCOPY。
Invoke-Sqlcmd命令是PowerShell和MS SQL Server之间的操作通道，NET命令用于在命令行环境下操作网络环境设置和网络共享；ROBOCOPY则是功能强大的文件备份复制命令。这三个命令的介绍可以参考以下微软的文档：
Invoke-Sqlcmd命令：
https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
net.exe命令行程序：
robocopy.exe命令行程序：
https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy
简单说说之所以放在C:\Users目录下面的原因。
这备份脚本文件需要安排由特定用户BackupOp定时运行，而出于安全性考虑，该特定用户应尽量受限，比如不允许远程登录，仅用于定时任务。为免运行期间因脚本文件安全性和用户权限交叉影响导致冲突，因此放置在C:\Users下，容易定位和修改脚本文件的文件安全属性，典型如直接指定BackupOps账号对脚本文件有“完全控制”权限。
由于无可避免地需要在脚本文件中嵌入共享密码，因此必须确保数据库服务器本身的安全加固设置已经到位！
计划任务的设置可以通过以下的截图来说明：
在创建计划任务的最后是要输入执行任务的用户的密码。
完成计划任务设置后，可以直接启动任务然后等待执行完成，观察执行日志和实际的执行结果是否符合预期情况。然后就是隔夜后检查定时执行的情况。
通过上述处理，我们就可以实现自动执行数据库备份，并在备份后把备份文件复制转移到另外的存储位置，实现数据备份多副本。
题图由无界版图AI生成
本站微信订阅号：
本页网址二维码：