微信订阅号二维码

本页内容二维码:

本栏目热门内容
  • Windows 系统安全基线及软...
  • CIS-CAT 配置评估工具介绍...
  • 修改audispd.conf解决审计...
  • 详细了解微软安全合规工具...
  • 从甲方角度介绍“CIS互联...
  • 2022年甲方个人网络安全运...
  • 业务导向安全策略#5员工培...
  • CIS关键安全控制措施#1总...
  • 微软安全合规工具包(SCT...
  • 安装RHEL/CentOS时如何选...
  • CIS关键安全控制措施#16应...
  • SELINUX介绍连载#3:什么...
  • CIS关键安全控制措施#17突...
  • CIS RAM风险评估方法5篇目...
  • 网络安全日志收集甲方基础...
  • SELINUX介绍连载#1:基础...
  • CIS关键安全控制措施#6访...
  • SELINUX介绍连载#2:模式...
  • 关于OpenSCAP/SCAP安全策...
  • CIS RAM#4实施防护措施、...
  • 什么是网络行为异常检测(...
  • CIS关键安全控制措施集18...
  • CIS关键安全控制措施#14安...
  • 网络攻防中的色彩象征
  • DNS子域名发掘工具两项
  • 业务导向安全策略#1数据管...
  • CIS RAM#1风险评估方法核...
  • 内网DNS服务器安全性能基...
  • CIS关键安全控制措施#8审...
  • 更多...

    MSSQL数据库自动备份和自动复制转移备份

    作者:Sender  来源:WaveCN.com  发布日期:2023-01-06  最后修改日期:2023-01-06

    标题图

    各色的数据如同堆叠的水晶,在平台的激发下透出诱人的霞光。
    (题图由无界版图AI生成)

     

    数据备份的重要性是老生常谈。但长期以来,企业组织对数据备份的认同度仅在于“不要丢了数据”。

    但“不要丢数据”这个要求,潜台词就是不仅要有做数据备份,还应该有不止一份备份才行。

    备份的手段有很多种,不差钱的甲方,可以购买各种专门的备份设备,比如国产的某数品牌备份系统,在笔者的工作环境中也还算是工作良好的。

    但无论差不差钱,刚才说过了,备份手段、备份数量都不能只有一种或一套,尤其是备份必须要有副本。

    因此,笔者就在自己的工作环境中,除了使用专用备份机进行备份之外,还通过自动化脚本去实现产生数据备份和转移数据备份的过程。

     

    本文涉及的知识点包括: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脚本。

     

    一、设置MS SQL 数据库维护计划执行备份

    首先我们需要通过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的存储过程去解决问题。存储过程代码篇幅有点长,这里不直接贴了,参见以下网址:

    https://www.mssqltips.com/sqlservertip/2167/custom-spstartjob-to-delay-next-task-until-sql-agent-job-has-completed/

     

    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命令,以及两个命令行程序:NETROBOCOPY

    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命令行程序:

    https://learn.microsoft.com/en-us/troubleshoot/windows-server/networking/net-commands-on-operating-systems

    robocopy.exe命令行程序:

    https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy

    简单说说之所以放在C:\Users目录下面的原因。

    这备份脚本文件需要安排由特定用户BackupOp定时运行,而出于安全性考虑,该特定用户应尽量受限,比如不允许远程登录,仅用于定时任务。为免运行期间因脚本文件安全性和用户权限交叉影响导致冲突,因此放置在C:\Users下,容易定位和修改脚本文件的文件安全属性,典型如直接指定BackupOps账号对脚本文件有“完全控制”权限。

    由于无可避免地需要在脚本文件中嵌入共享密码,因此必须确保数据库服务器本身的安全加固设置已经到位!

     

    五、设置计划任务,定时执行自动转移备份脚本

     

    计划任务的设置可以通过以下的截图来说明:

     

    在创建计划任务的最后是要输入执行任务的用户的密码。

     

    完成计划任务设置后,可以直接启动任务然后等待执行完成,观察执行日志和实际的执行结果是否符合预期情况。然后就是隔夜后检查定时执行的情况。

     

    六、总结

     

    通过上述处理,我们就可以实现自动执行数据库备份,并在备份后把备份文件复制转移到另外的存储位置,实现数据备份多副本。

    欢迎关注微信公众号后私信讨论文章内容!
    本栏目相关
  •  2022-03-16 Windows 系统安全基线及软件工具介绍
  •  2022-05-11 CIS-CAT 配置评估工具介绍及操作实践
  •  2022-03-17 详细了解微软安全合规工具包(SCT)
  •  2022-03-25 从甲方角度介绍“CIS互联网安全中心”
  •  2022-05-10 2022年甲方个人网络安全运维基础工具
  •  2022-06-06 业务导向甲方网络安全策略设计#5员工培训及系列总结
  •  2022-03-28 如何应用CIS互联网安全中心发布的《CIS关键安全控制措施集》之一:总览
  •  2022-03-18 微软安全合规工具包(SCT)操作实战
  •  2022-03-11 安装RHEL/CentOS时如何选择配置安全策略?
  • 返回页首