SQL Server数据库自动备份方法

测试环境如下 ：两台数据库服务器（可以是实体机 ，也 可以是虚拟机） ：服务器 A 、服务器 B 。 服务器 A 做主机 ， IP 地址 １０ ．０ ．０ ．１ ；服务器 B 做备用机 ，IP 地址 １０ ．０ ．０ ．２ 。 操作 系 统 ：Window s２００８ R２ 。 数 据 库 ：SQL Server２００８ R２ 。 数据库名 ：lyxy 。
ＳＱＬ Ｓｅｒｖｅｒ 数据库自动备份方法
仝 虎１ ，周 进２
（１ ．南京旅游职业学院 电教中心 ，江苏 南京 ２１１１００ ；２ ．南京恩瑞特实业有限公司 ，江苏 南京 ２１１１０６）
摘 要 ：数据库是应用系统的核心 ，数据库备份极其重要 。 以 SQL Server２００８ R２ 数据库为例 ，介绍了利用 SQL Server 代理创建作业实现数据库自动异地备份功能的具体方法 。 该方法思路清晰 ，操作简单 ，能够有效实现数据库的 异地备份 、多机备份 。 关键词 ：数据库 ；SQL Server ；自动本地备份 ；自动异地备份 DOI ：１０ ．１１９０７ ／rjdk ．１４３１０６３ 中图分类号 ：T P３０１ 文献标识码 ：A 文章编号 ：１６７２‐７８００（２０１５）００５‐００６０‐０２
3 结语
图 2 作业执行
作业创建完成后 ，可以选中该作业 ，然后点击右键 ，选 中“作业开始步骤” ，测试该异地备份是否成功执行 。 这时 很可能会执行失败 ，出现一条和 xp＿cmdshell 相关的错误 信息［４］ ，即“基于安全考虑 ，在 SQL Server２００８ R２ 版本中 xp＿cmdshell 默认是关闭状态［４‐５］ ” 。 所以在执行异地备份 数据库之前必须打开它 ，具体命令语句可参考文献 ［５ ］ 。 启用 xp＿cmdshell 后 ，即可成功执行备份作业 。 基于安全 考虑 ，在执行完备份操作后还应将 xp＿ cmdshell 功能关 闭 。 为了实现真正的自动异地备份功能 ，可以将开启和关 闭 xp＿cmdshell 的过程也写到作业里 。 具体做法是 ，编辑 上述自动异地备份作业 lyxy＿ydfullbk＿job ，在步骤 ４ 中 ， 将 T‐SQL 语句进行扩充并完善如下 ：
作者简介 ：仝虎（１９７９ － ） ，男 ，河南修武人 ，硕士 ，南京旅游职业学院电教中心工程师 ，研究方向为数据库管理系统与决策支持系统 。
第 ５ 期 仝 虎 ，周 进 ：SQL Server 数据库自动备份方法
· ６１ ·
－ － 关闭 xp＿cmdshell 并禁用配置高级选项 EXEC sp＿configure ＇xp＿cmdshell＇，０ RECON FIGU RE EXEC sp＿configure ＇show advanced options＇，０ RECON FIGU RE 执行频率 、触发时间等都不变 ，至此可以完全实现真 正的自动异地备份功能 。 若想实现多机备份 ，则多创建几 个类似作业即可 。
0 引言
近年来 ，以数据库为核心的计算机管理信息系统的普 及 ，使得现代企事业单位的工作效率大大提高 ，但同时也 加大了现代企事业单位对计算机软硬件的依赖 。 单位的 业务数据大都存储在数据库服务器上 ，因而数据库安全问 题尤为重要 。 保障数据库安全的最主要方法就是做好数 据库备份工作 ，不但要做好手工备份 ，更要做好自动备份 ； 不但要做好本地备份 ，更要做好异地备份 ，以防止工作服 务器出现灾难性硬件故障从而造成不可挽回的损失 。 本 文介绍了如何利用 SQL Server 代理服务器创建作业来实 现数据库的自动异地备份功能 ，思路清晰 ，操作简单 。
DECLARE ＠ strPath NVARCHAR（２００） set ＠ strPath ＝ convert （NVARCHAR （１９ ） ，getdate （ ） ， １２０ ） set ＠ strPath ＝ REPLACE（＠ strPath ，＇：＇ ，＇） set ＠ strPath ＝ ＇E ：＼DB＿BK’ ＋ ＇lyxy＇＋ ＠ strPath ＋ ＇．bak＇ BACK U P DA T ABASE ［lyxy ］ TO DISK ＝ ＠ strPath WIT H NOINIT ，NOU NLOAD ，NOSKIP ，ST A TS ＝ １０ ，NO‐ FORM AT 步骤 ５ ：点击新建按钮 ，添加计划 ，输入名称 lyxy＿full‐ bk＿plan ，设置好执行频率和具体触发时间等 。 步骤 ６ ：点击确定 ，完成 。 完成后 ，可以选中该作业 ， 然后点击右键 ，选中“作业开始步骤” ，测试该作业是否成 功执行 ，若成功则会出现如图 ２ 所示界面 。
参考文献 ：
［１］ CHRIS LEIT ER ，DAN WOOD ．SQL Server２００８ DBA 入门经典 ［M ］ ．张德群 ，译 ．北京 ：清华大学出版社 ，２０１０ ．
［２］ CHRIST IAN BOL TON ，JUST IN LANGFORD ，BREN T OZAR ，et al ．Professional SQL server ２００８ internals and troubleshooting ［M ］ ．Wiley ，２００９ ．
本文以 SQL Server２００８ R２ 数据库为例 ，先描述了利 用作业实现数据库自动本地备份的过程 ，然后在此基础上 介绍了如何创建自动异地备份数据库作业 ，最后指出作业 执行失败的原因和改正办法 ，并完善了自动异地备份数据 库作业 ，实现了真正意义上的数据库自动异地备份功能 。 实践证明 ，可以利用该备份方法实现数据库的异地备份 、 多机备份 。
2 自动异地备份
SQL Server 数据库异地备份主要通过映射网络盘和 执行 cmdshell 命令来实现［２‐３］ 。 假设登录服务器 B （１０ ．０ ． ０ ．２）的帐号为 administrator ，密码为 １１１ ，首先在备用服 务器 B 上创建文件夹 E ：＼DB＿BK 。 执行过程同创建自动 本地备份作业的过程一样 ，区别在于步骤 ４ 中 T‐SQL 语 句的不同 ，异地备份要在备份命令语句 BACKU P DA T A‐ BASE 前加 上 一 条语 句 ：exec master ．．xp＿ cmdshell ＇net use ＼１０ ．０ ．０ ．２＼E ＄ １１１ ／user ：１０ ．０ ．０ ．２ ＼administrator＇。
步骤 ２ ：启动 SQL Server 代理服务 ，如图 １ 所示 。
步骤 ３ ：点击作业 — 新建作业 ，在 ［常规 ］选项中输入 作业名称 lyxy＿fullbk＿job 。
步骤 ４ ：点击新建按钮 ，新建步骤 ，输入名称 lyxy＿full‐ bk＿bz ，类型选 T‐SQL ，数据库选择 master ，在命令的空白 处输入如下语句 ，然后点击确定 ，返回 。
［３］ 刘笑凯 ，张水平 ，毛云飞 ．SQL Server 的备份策略应用 ［J］ ．计算机 工程 ，２００３ ，２９（６） ：１９３‐１９４ ．
［４］ 吴彦 ，张冬 ．SQL Server 自动异地备份的研究与应用 ［J］ ．电脑知识 与技术 ，２００９（５） ：９６１７‐９６１９ ．
［５ ］ JONA T HAN KEHAYIAS ，T ED KRU EGER ．T roubleshooting SQL Server ——— a guide for the accidental DBA ［M ］ ．Red gate books ，２０１１ ． （责任编辑 备份的基础 ，先介绍如何利 用 SQL Server 作业实现本地定时自动备份数据库 。 每天 备份的文件以日期时间命名 ，存放在工作服务器 A （１０ ．０ ． ０ ．１）的文件夹 E ：＼DB＿BK ＼下 。 具体操作步骤［１］ 如下 ：
步骤 １ ：打开 SSM S （SQL Server M anagement Studi‐ o） 。
图 1 SQL Server 代理服务
set ＠ strPath ＝ convert （NVARCHAR （１９ ） ，getdate （） ， １２０ ）
set ＠ strPath ＝ REPLACE（＠ strPath ，＇：＇ ，＇） set ＠ strPath ＝ ＇＼１０ ．０ ．０ ．２ ＼E ＄ ＼DB＿BK’ ＋ ＇lyxy＇＋ ＠ str‐ Path ＋ ＇．bak＇ exec master ．．xp＿cmdshell ＇net use ＼１０ ．０ ．０ ．２ ＼E ＄ １１１ ／us‐ er ：１０ ．０ ．０ ．２＼administrator＇ BACK U P DA T ABASE ［lyxy ］ TO DISK ＝ ＠ strPath WIT H NOINIT ，NOU NLOAD ，NOSKIP ，ST A TS ＝ １０ ，NO‐ FORM AT
－ － 允许配置高级选项并启用 xp＿cmdshell EXEC sp＿configure ＇show advanced options＇，１
RECON FIGU RE
EXEC sp＿configure ＇xp＿cmdshell＇，１
RECON FIGU RE － － 执行异地备份
DECLARE ＠ strPath NVARCHAR（２００）