SQL Server数据库自动备份方法

本文以 SQL Server２００８ R２ 数据库为例 ，先描述了利 用作业实现数据库自动本地备份的过程 ，然后在此基础上 介绍了如何创建自动异地备份数据库作业 ，最后指出作业 执行失败的原因和改正办法 ，并完善了自动异地备份数据 库作业 ，实现了真正意义上的数据库自动异地备份功能 。 实践证明 ，可以利用该备份方法实现数据库的异地备份 、 多机备份 。
测试环境如下 ：两台数据库服务器（可以是实体机 ，也 可以是虚拟机） ：服务器 A 、服务器 B 。 服务器 A 做主机 ， IP 地址 １０ ．０ ．０ ．１ ；服务器 B 做备用机 ，IP 地址 １０ ．０ ．０ ．２ 。 操作 系 统 ：Window s２００８ R２ 。 数 据 库 ：SQL Server２００８ R２ 。 数据库名 ：lyxy 。
图 1 SQL Server 代理服务
set Biblioteka Baidu 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 ＇xp＿cmdshell＇，０ RECON FIGU RE EXEC sp＿configure ＇show advanced options＇，０ RECON FIGU RE 执行频率 、触发时间等都不变 ，至此可以完全实现真 正的自动异地备份功能 。 若想实现多机备份 ，则多创建几 个类似作业即可 。
作者简介 ：仝虎（１９７９ － ） ，男 ，河南修武人 ，硕士 ，南京旅游职业学院电教中心工程师 ，研究方向为数据库管理系统与决策支持系统 。
第 ５ 期 仝 虎 ，周 进 ：SQL Server 数据库自动备份方法
· ６１ ·
参考文献 ：
［１］ 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 ，２００９ ．
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＇。
0 引言
近年来 ，以数据库为核心的计算机管理信息系统的普 及 ，使得现代企事业单位的工作效率大大提高 ，但同时也 加大了现代企事业单位对计算机软硬件的依赖 。 单位的 业务数据大都存储在数据库服务器上 ，因而数据库安全问 题尤为重要 。 保障数据库安全的最主要方法就是做好数 据库备份工作 ，不但要做好手工备份 ，更要做好自动备份 ； 不但要做好本地备份 ，更要做好异地备份 ，以防止工作服 务器出现灾难性硬件故障从而造成不可挽回的损失 。 本 文介绍了如何利用 SQL Server 代理服务器创建作业来实 现数据库的自动异地备份功能 ，思路清晰 ，操作简单 。
ＳＱＬ Ｓｅｒｖｅｒ 数据库自动备份方法
仝 虎１ ，周 进２
（１ ．南京旅游职业学院 电教中心 ，江苏 南京 ２１１１００ ；２ ．南京恩瑞特实业有限公司 ，江苏 南京 ２１１１０６）
摘 要 ：数据库是应用系统的核心 ，数据库备份极其重要 。 以 SQL Server２００８ R２ 数据库为例 ，介绍了利用 SQL Server 代理创建作业实现数据库自动异地备份功能的具体方法 。 该方法思路清晰 ，操作简单 ，能够有效实现数据库的 异地备份 、多机备份 。 关键词 ：数据库 ；SQL Server ；自动本地备份 ；自动异地备份 DOI ：１０ ．１１９０７ ／rjdk ．１４３１０６３ 中图分类号 ：T P３０１ 文献标识码 ：A 文章编号 ：１６７２‐７８００（２０１５）００５‐００６０‐０２
［３］ 刘笑凯 ，张水平 ，毛云飞 ．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 ，２０１１ ． （责任编辑 ：孙 娟 ）
1 自动本地备份
自动本地备份是自动异地备份的基础 ，先介绍如何利 用 SQL Server 作业实现本地定时自动备份数据库 。 每天 备份的文件以日期时间命名 ，存放在工作服务器 A （１０ ．０ ． ０ ．１）的文件夹 E ：＼DB＿BK ＼下 。 具体操作步骤［１］ 如下 ：
步骤 １ ：打开 SSM S （SQL Server M anagement Studi‐ o） 。
－ － 允许配置高级选项并启用 xp＿cmdshell EXEC sp＿configure ＇show advanced options＇，１
RECON FIGU RE
EXEC sp＿configure ＇xp＿cmdshell＇，１
RECON FIGU RE － － 执行异地备份
DECLARE ＠ strPath NVARCHAR（２００）
3 结语
图 2 作业执行
作业创建完成后 ，可以选中该作业 ，然后点击右键 ，选 中“作业开始步骤” ，测试该异地备份是否成功执行 。 这时 很可能会执行失败 ，出现一条和 xp＿cmdshell 相关的错误 信息［４］ ，即“基于安全考虑 ，在 SQL Server２００８ R２ 版本中 xp＿cmdshell 默认是关闭状态［４‐５］ ” 。 所以在执行异地备份 数据库之前必须打开它 ，具体命令语句可参考文献 ［５ ］ 。 启用 xp＿cmdshell 后 ，即可成功执行备份作业 。 基于安全 考虑 ，在执行完备份操作后还应将 xp＿ cmdshell 功能关 闭 。 为了实现真正的自动异地备份功能 ，可以将开启和关 闭 xp＿cmdshell 的过程也写到作业里 。 具体做法是 ，编辑 上述自动异地备份作业 lyxy＿ydfullbk＿job ，在步骤 ４ 中 ， 将 T‐SQL 语句进行扩充并完善如下 ：
步骤 ２ ：启动 SQL Server 代理服务 ，如图 １ 所示 。
步骤 ３ ：点击作业 — 新建作业 ，在 ［常规 ］选项中输入 作业名称 lyxy＿fullbk＿job 。
步骤 ４ ：点击新建按钮 ，新建步骤 ，输入名称 lyxy＿full‐ bk＿bz ，类型选 T‐SQL ，数据库选择 master ，在命令的空白 处输入如下语句 ，然后点击确定 ，返回 。
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 ，设置好执行频率和具体触发时间等 。 步骤 ６ ：点击确定 ，完成 。 完成后 ，可以选中该作业 ， 然后点击右键 ，选中“作业开始步骤” ，测试该作业是否成 功执行 ，若成功则会出现如图 ２ 所示界面 。