SQL Server数据库自动备份方法

摘 要：数据库是应用系统的核心，数据库备份极其重要。以SQL Server2008 R2数据库为例，介绍了利用SQL Server创建作业实现数据库自动异地备份功能的具体方法。该方法思路清晰，操作简单，能够有效实现数据库的异地备份、多机备份。

关键词：数据库；SQL Server；自动本地备份；自动异地备份

中图分类号：TP301

文献标识码：A 文章编号：1672-7800（2015）005-0060-02

作者简介：仝虎（1979-），男，河南修武人，硕士，南京旅游职业学院电教中心工程师，研究方向为数据库管理系统与决策支持系统。

0 引言

近年来，以数据库为核心的计算机管理信息系统的普及，使得现代企事业单位的工作效率大大提高，但同时也加大了现代企事业单位对计算机软硬件的依赖。单位的业务数据大都存储在数据库服务器上，因而数据库安全问题尤为重要。保障数据库安全的最主要方法就是做好数据库备份工作，不但要做好手工备份，更要做好自动备份；不但要做好本地备份，更要做好异地备份，以防止工作服务器出现灾难性硬件故障从而造成不可挽回的损失。本文介绍了如何利用SQL Server服务器创建作业来实现数据库的自动异地备份功能，思路清晰，操作简单。

测试环境如下：两台数据库服务器（可以是实体机，也可以是虚拟机）：服务器A、服务器B。服务器A做主机，IP地址10.0.0.1；服务器B做备用机，IP地址10.0.0.2。操作系统：Windows2008 R2。数据库：SQL Server2008 R2。数据库名：lyxy。

1 自动本地备份

自动本地备份是自动异地备份的基础，先介绍如何利用SQL Server作业实现本地定时自动备份数据库。每天备份的文件以日期时间命名，存放在工作服务器A（10.0.0.1）的文件夹E：＼＼DB_BK＼下。具体操作步骤[1]如下：

步骤1：打开SSMS（SQL Server Management Studio）。

步骤2：启动SQL Server服务，如图1所示。

步骤3：点击作业―新建作业，在[常规]选项中输入作业名称lyxy_fullbk_job。

步骤4：点击新建按钮，新建步骤，输入名称lyxy_fullbk_bz，类型选T-SQL，数据库选择master，在命令的空白处输入如下语句，然后点击确定，返回。

DECLARE @strPath NVARCHAR（200）

set @strPath = convert（NVARCHAR（19），getdate（），120）

set @strPath = REPLACE（@strPath，'：' ，''）

set @strPath = 'E：＼＼DB_BK＼' + 'lyxy'+@strPath + '.bak'

BACKUP DATABASE [lyxy] TO DISK = @strPath WITH NOINIT ，NOUNLOAD ，NOSKIP ，STATS = 10，NOFORMAT

步骤5：点击新建按钮，添加计划，输入名称lyxy_fullbk_plan，设置好执行频率和具体触发时间等。

步骤6：点击确定，完成。完成后，可以选中该作业，然后点击右键，选中“作业开始步骤”，测试该作业是否成功执行，若成功则会出现如图2所示界面。

2 自动异地备份

SQL Server数据库异地备份主要通过映射网络盘和执行cmdshell命令来实现[2-3]。假设登录服务器B（10.0.0.2）的帐号为administrator，密码为111， 首先在备用服务器B上创建文件夹E：＼＼DB_BK。执行过程同创建自动本地备份作业的过程一样，区别在于步骤4中T-SQL语句的不同，异地备份要在备份命令语句BACKUP DATABASE前加上一条语句：exec master..xp_cmdshell 'net use ＼＼10.0.0.2＼＼E$ 111 /user：10.0.0.2＼＼administrator'。

作业创建完成后，可以选中该作业，然后点击右键，选中“作业开始步骤”，测试该异地备份是否成功执行。这时很可能会执行失败，出现一条和xp_cmdshell相关的错误信息[4]，即“基于安全考虑，在SQL Server2008 R2版本中xp_cmdshell 默认是关闭状态[4-5]”。所以在执行异地备份数据库之前必须打开它，具体命令语句可参考文献[5]。启用xp_cmdshell后，即可成功执行备份作业。基于安全考虑，在执行完备份操作后还应将xp_cmdshell功能关闭。为了实现真正的自动异地备份功能，可以将开启和关闭xp_cmdshell的过程也写到作业里。具体做法是，编辑上述自动异地备份作业lyxy_ydfullbk_job，在步骤4中，将T-SQL语句进行扩充并完善如下：

--允许配置高级选项并启用xp_cmdshell

EXEC sp_configure 'show advanced options'，1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell'，1

RECONFIGURE

--执行异地备份

DECLARE @strPath NVARCHAR（200）

set @strPath = convert（NVARCHAR（19），getdate（），120）

set @strPath = REPLACE（@strPath，'：' ，''）

set @strPath = '＼＼10.0.0.2＼＼E$＼＼DB_BK＼' + 'lyxy'+@strPath + '.bak'

exec master..xp_cmdshell 'net use ＼＼10.0.0.2＼＼E$ 111 /user：10.0.0.2＼＼administrator'

BACKUP DATABASE [lyxy] TO DISK = @strPath WITH NOINIT ，NOUNLOAD ，NOSKIP ，STATS = 10，NOFORMAT

--关闭xp_cmdshell并禁用配置高级选项

EXEC sp_configure 'xp_cmdshell'，0

RECONFIGURE

EXEC sp_configure 'show advanced options'，0

RECONFIGURE

执行频率、触发时间等都不变，至此可以完全实现真正的自动异地备份功能。若想实现多机备份，则多创建几个类似作业即可。

3 结语

本文以SQL Server2008 R2数据库为例，先描述了利用作业实现数据库自动本地备份的过程，然后在此基础上介绍了如何创建自动异地备份数据库作业，最后指出作业执行失败的原因和改正办法，并完善了自动异地备份数据库作业，实现了真正意义上的数据库自动异地备份功能。实践证明，可以利用该备份方法实现数据库的异地备份、多机备份。

参考文献：

[1] CHRIS LEITER，DAN WOOD.SQL Server2008 DBA入门经典[M].张德群，译.北京：清华大学出版社，2010.

[2] CHRISTIAN BOLTON，JUSTIN LANGFORD，BRENT OZAR，et al.Professional SQL server 2008 internals and troubleshooting [M].Wiley，2009.

[3] 刘笑凯，张水平，毛云飞.SQL Server 的备份策略应用[J].计算机工程，2003，29（6）：193-194.

[4] 吴彦，张冬.SQL Server自动异地备份的研究与应用[J].电脑知识与技术，2009（5）：9617-9619.

[5] JONATHAN KEHAYIAS，TED KRUEGER.Troubleshooting SQL Server――a guide for the accidental DBA[M].Red gate books，2011.