SQL SERVER数据库自动备份与还原方法

李光辉

（江西广播电视台 江西省南昌市 330046）

SQL Sever 数据库系统较Oracle、Sybase 等大型数据库系统有易用性、易维护的优势，多用于中小型系统中。在SQL Sever 数据库系统应用中，主要是采用组合多种高可用技术的方案来提高数据库系统的可靠性，如：双机服务器群集+微软群集管理功能或第三方故障检测迁移软件、镜像双机热备+见证服务器等。此外，还需配置备份数据库系统，如何安全可靠地实现主备数据库系统之间的数据同步十分重要，实现的方式有多种，如配置微软的发布/订阅功能、编写存储过程等，本文详细介绍了一种基于批处理和T-SQL语句实现SQL Server 数据库自动备份与还原的方法，该方法简单可靠。

本文介绍的数据库自动备份与还原方法，在双机服务器群集+微软群集管理功能和镜像双机热备+见证服务器两种高可用性数据库架构中，平稳应用多年。

为方便描述，下文以主数据库架构为双机服务器群集+微软群集管理功能为例进行介绍，数据库服务器操作系统为Windows server2008 R2。数据库版本为SQL Server 2008 R2。

1 数据库备份自动还原设计

双机服务器配置为SQL Server 故障转移群集，保证主备节点故障时的自动切换，实现数据库的高可用性。群集系统架构如图1。

主备数据库数据同步流程如图2。

要实现以上流程按时自动完成，需要对流程中的三步进行详细的设计，其中第一步，通过建立“维护计划”实现；第二步，通过编辑简单的批处理命令实现，批处理程序每天按时由“任务计划程序”自动运行；第三步，通过建立“维护计划”，计划中的任务为“执行T-SQL 语句”任务，通过T-SQL 语句RESTORE DATABASE 恢复数据库。

由于SQL Server 故障转移群集依赖于域环境，故需要配置域控服务器，需要注意的是，域控不可与SQL 数据库软件部署在同一台服务器上，否则SQL 数据库软件将无法完成安装，并且无法回退。为了安全可靠，域控应配置为主备模式，即域控需要使用两台服务器来承担，一台为主域控，一台为备域控。两台主数据库服务器共享磁盘阵列，磁盘阵列分为数据盘和仲裁盘，数据盘用于数据库数据文件的存放，仲裁盘用于协调集群节点间的故障转移，仲裁盘位于共享磁盘阵列中，能保证所有节点都能够访问到。备数据库数据文件存在本机磁盘中。

2 数据库备份自动还原实现

2.1 自动定时备份数据库

在主数据库系统中为每个数据库建立维护计划，维护计划内容含两个任务：

（1）设置“备份数据库（完整）”任务，设置备份文件存放的目录及文件夹，将此文件夹设置为共享，并设置访问权限。勾选验证备份完整性。

（2）设置“清除维护”任务，完成对超过一定时间（如两周）的备份文件进行删除。

图1：数据库架构图

图2：数据同步流程图

根据系统中数据库的数量和系统业务情况，设置好每天维护计划的数量和执行次数，维护计划的执行时间，应设定为每天系统空闲时，如我台高清播出系统中设置了两个维护计划，每天中午13点和凌晨2 点各执行一次。

2.2 复制备份文件至备份数据库

在备份数据库中，建立名为“周期备份”和“当日备份”两个文件夹，分别用来存放一段时间的备份文件和当日备份文件。在备份数据库系统中编写批处理程序，程序主要功能如下（以下蓝色部分表示批处理语句）：

（1）将主数据系统的备份文件复制至周期文件夹中。

xcopy \主数据库备份文件目录路径*.bak 备数据库备份文件目录路径周期备份 /y /d:%month%-%day%-%year%

xcopy 为复制文件命令，其中/y 参数指定复制时，不会提示以确认要覆盖现有目标文件，/d:%date:~5,2%-%date:~8,2%-%date:~0,4% 指定只复制当前系统日期的文件。

注意/d 指定的日期必须是“MM-DD-YYYY”的格式。

（2）保留一段时间的备份文件。

forfilеs /p 备数据库备份文件目录路径周期备份 /m *.bak /d -15 /c "cmd /c del /f @path"

forfilеs 为文件处理命令，/p 指定目录路径，/m 指定查找文件名掩码，/d -15 表示指定当前日期的15 日前，/c "cmd /c del /f @path"表示为目录下每个文件执行强制删除命令。

（3）复制当日备份文件至当日备份文件夹。

xcopy 备数据库备份文件目录路径周期备份*.bak 备数据库备份文件目录路径当日备份 /y /d:%month%-%day%-%year%

（4）删除当日备份文件夹文件，重命名拷贝文件夹为数据库名，因需要指定只复制当天生成的文件，此处拷贝时不可以使用copy命令来完成。

盘符:

cd 备数据库备份文件目录路径当日备份

进入备数据库备份文件所在目录路径。

del 数据库名.bak

删除数据库文件。

rename *.bak 数据库名.bak

重命名复制来的当日数据库备份文件名为数据库名。

将以上蓝色部分命令的路径部分替换为实际路径，批处理程序即可运行。

2.3 创建任务计划程序，执行批处理文件

在备数据库系统操作系统中创建任务计划程序，在创建任务窗口中进行设置，在常规页签中设置计划名，在触发器页签中设置执行时间和频次，勾选启用，执行时间要确保为每天备份文件生成之后，在操作页签中设置批处理存放路径，并将操作设定为“启动程序”。

2.4 设置维护计划，执行T-SQL语句还原数据库

在备数据库系统中为每个数据库建立维护计划，维护计划任务为“执行T-SQL 语句”，每天按时用备份文件还原数据库，特别注意的是计划执行的时间设置为复制任务完成之后。

T-SQL 语句如下：

RESTORE DATABASE [数据库名]FROM DISK = N' 当 日备份路径当日备份文件名.bak' WITH FILE = 1,MOVE N'数据库名' TO N'备数据库数据文件路径数据库名.mdf',MOVE N'数据库名_log' TO N'备数据库数据文件路径数据库名_log.ldf', NOUNLOAD,REPLACE,STATS = 10

GO

3 维护实践中的几个要点

在SQL server 群集数据库的日常运维中，除对数据库进行备份还原操作外，还需要进行如日志清除、数据库文件收缩、应急等必要操作。

3.1 日志清除

系统长时间运行或系统设计等原因，可能导致数据库中的某些表数据量过大，导致数据库性能显著下降，需要对数据库日志表进行清理。在清理进行之前，需要提前备份数据库，对待清除日志进行备份保留，以防排查问题需要查询以前的数据。

（1）新建2 个查询，一个用于查询日志表记录数，另一个用于删除日志表记录；

（2）查询记录数：select count(*) from Log_table

Log_table 是需查询的表名，结果可以查看有多少行数据，如果数据量过多就需要删除数据。

（3）删除记录：

1.部分删除表中日志记录：delete top(3000000) from Log_table

表示删除Log_table 表前3000000 行数据记录，用于部分删除表中日志记录，因为删除过程是从表中一行一行删除，并且将该删除操作记入日志中保存以便进行回滚操作，删除过程较缓慢。

2.全部删除表中日志记录：truncate table Log_table

表示删除表中全部日志记录，但表结构保持不变。因为该方式采用直接释放数据页的方式来删除记录，删除速度快，效率高，但操作立即生效，不能回滚。

（4）选中要操作的数据库，执行②中的查询记录语句，如果查询结果行数过大，如达百万级，则需考虑执行删除操作，如需保留部分日志记录，则需要使用delete 语句进行操作，不能够使用truncate 进行全部删除，需注意在使用delete 语句时，一次执行删除数量不宜太大，如不应超过一百万，数量越大，执行时间越长，应尽量避开业务高峰期操作。

3.2 收缩数据库文件

由于SQL server 对数据库空间的分派采取的是“先分派、后使用”的方式，所以数据库在使用的过程中就可能会存在多余的空间，在一定程度上造成存储空间的浪费，数据库文件增长到一定大小后，需对数据库文件进行收缩操作。

数据库允许对每个文件进行收缩，直至收缩到没有剩余的可用空间为止。可以自动或手动收缩。数据库物理文件分为数据库文件“*.mdf”和日志文件“*.ldf”。通常日志文件较大。此处以手动收缩为例进行介绍。

（1）登录数据库管理工具，在需要操作的数据库上右健点击，在弹出菜单中选择依次选择“任务”/“收缩”/“数据库”。若只要收缩单个数据库文件，在菜单中选择依次选择“任务”/“收缩”/“文件”。

（2）在“收缩数据库”对话框中，如可用空间百分比大于0 时，收缩可释放空间，点击确定可释放空间。收缩幅度不应太大，如每次500M 大小，一次收缩空间过大，会导致磁盘无法响应。

如果收缩未能有效减少数据库文件所占用的空间，可考虑对数据库进行日志文件重建操作。此操作需要分离数据库，故需要中断数据库的连接。

3.3 数据库群集故障应急措施

在数据库出现异常时，不能任意重启或断电，一般应急手段均是启用备份文件。因此要求系统维护人员每天检查自动备份批处理状态，确保其每天按时成功执行，查看备份存储路径下的备份文件是否定时生成，生成时间和文件大小是否正常，并每日定期检查备数据库服务器的业务数据库是否按时成功还原。

如果主数据库群集发生宕机，两个节点均不能提供正常服务。需立即启动应急预案，启用备数据库：

（1）检查备数据库SQL 服务工作状态，验证数据库中的数据为最近恢复的数据。

（2）修改所有客户端软件的数据库连接地址为备数据库服务器地址。

（3）客户端重连数据库或重启客户端软件，恢复业务。

一旦事故发生，在客户端较多的情况下，以上方法至少需要10 分钟完成。经过我们多次实测，可以通过直接将备数据库服务器的IP 地址，改为数据库群集的虚拟IP 地址，让备数据库直接顶替数据库群集工作，省去了以上（2）和（2）步的操作，3 分钟以内就可以恢复业务。

4 总结

本文介绍的基于批处理和T-SQL 语句的SQL Sever 备份数据库自动与还原方法，经济、简单、实用，已应用在我台的两套高清播出系统中，至今已平稳可靠运行近6年，极大地减轻了运维人员工作压力。