SQL Server2005自动备份的三种方法

SQL Server2005自动备份的三种方法

刚接手SQL Server数据库的维护，还是菜鸟一个。由于公司大部分项目都是使用这种类型的数据库，于是先研究了下其备份方法。总结有如下三种：

1.完全使用SQL Server自带的自动备份机制。在这种方式中，通过维护计划调用SQL

Server内置的“备份数据库”任务并配置定时作业实现。

2.不使用维护计划，在定时作业中配置备份命令。

3.不使用维护计划，在master库中编写备份的存储过程，然后配置定时作业调用该存

储过程。

下面以SQL Server2005上的操作为例进行说明。

1.操作前提

要使SQL Server中的定时作业能够正确运行，必须先启动SQL Server代理。SQL Server代理主要提供周期性任务、服务器异常时给sa发送问题告警。详细的解释可参考如下链接：/en-us/library/aa174509(v=sql.80).aspx。

SQL Server的启动方法如下：

1.在windows下选择“开始”-》“控制面板”-》“管理工具”-》“服务”，或是选

择“开始”-》“运行”，在cmd窗口键入“services.msc”，进入到“服务”界面。

2.找到SQL Server Agent服务，启动该服务，并将启动类型配置为“自动”。

2.使用自动备份机制

使用维护计划向导来配置维护计划。操作步骤如下：

1.使用SQL Server Management Studio打开服务器，选择“管理”-》“维护计划”-》

“维护计划向导”。

2.维护计划向导启动界面中列出了维护计划的主要作用，其中就包含数据库备份。

3.设置维护计划名称。

4.选择“维护任务”。在这里可以根据指定的备份策略选择全量备份还是增量备份。

5.选择维护任务顺序。如果上一步选择多个维护任务，则需要在此设置各个维护任务的

顺序。

6.配置维护任务。选择待备份的数据库及备份文件存放路径。

7.定义维护计划属性。在“选择维护计划属性”窗口，点击“更改”进行设置。这里主

要设置维护计划对应的定时作业。

8.设置维护操作报告选项。维护操作报告可以txt形式存放在数据库运行目录下，也通

过邮件发给相关人员。请根据实际需要配置。这里选择默认值。

9.完成维护计划的创建。显示如下界面表明维护计划创建成功。

10.结果检查。在“管理”-》“维护计划”中可看到新建的维护计划，同时在“SQL Server

代理”-》“作业”中也可看到同名的定时作业。

双击新建的维护计划，查看其属性，可看到维护计划调用了SQL Server自带的“备份数据库”任务。

3.使用备份命令

使用用户自定义的备份命令时，只需要在配置定时作业时增加备份命令，即可实现自动备份。具体的操作方法如下：

1.使用SQL Server Management Studio打开服务器，选择“SQL Server代理”-》“作

业”，右键选择“新建作业”。

2.在“新建作业”窗口中，选择“常规”页，设置作业名称。其他属性默认。

3.选择“步骤”页，点击“新建”，设置作业步骤。输入步骤名称，类型选择

“Transact-SQL脚本（T-SQL）”，选择要备份的数据库，在命令属性中输入如下代码：

DECLARE @strPath NVARCHAR(200)

set @strPath = convert(NVARCHAR(19),getdate(),120)

set @strPath = REPLACE(@strPath, ':' , '-')

set @strPath = 'F:\DB_Bak\' +'DB_Bak' +@strPath + '.bak'

BACKUP DATABASE test1 TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

蓝色部分F:\DB_Bak\表示备份文件的存放目录，DB_Bak表示备份文件以

DB_Bak开头。备份文件的格式为“DB_Bakyyyy-mm-dd hh-mi-ss.bak”。

4.选择“计划”页，点击“新建”，设置作业计划。设置计划的名称、类型、执行

频率、执行间隔及开始结束时间。

5.设置完成后，点击“确定”。其他页可不设置。

6.检查结果。在“SQL Server代理”-》“作业”下，可看到新建的作业。选中作业，

右键选择“属性”，可查看该作业对应的各项设置。

4.使用存储过程

如果需要在同一服务器上备份多个数据库，可选择使用存储过程进行自动备份。具体的操作步骤如下：

1.使用SQL Server Management Studio打开服务器，选择“数据库”-》“系统数据

库”-》“master”-》“可编程性”-》“存储过程”，右键选择“新建存储过程”。

2.在存储过程创建窗口中，输入如下内容：

USE[master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Batch submitted through debugger:

SQLQuery1.sql|0|0|C:\Users\Administrator\AppData\Local\Temp\2\~vs A9AA.sql

CREATE PROC[dbo].[BackupYLData]

AS

DECLARE @strDate AS VARCHAR(20)

DECLARE @strFileName As VARCHAR(100)

DECLARE @strCommand AS VARCHAR(255)

SET @strDate=CONVERT(VARCHAR,GETDATE(), 112)

--备份test1数据库

SET @strFileName ='F:\DB_Bak\test1_bak_'+@strDate;

EXEC('BACKUP DATABASE test1TO DISK='''+@strFileName+'.dat''')

SET @strCommand='HaoZipC a -t7z'+@strFileName+'.7z

'+@strFileName+'.dat'

EXEC master..xp_cmdshell @strCommand

IF@@ERROR=0

BEGIN

SET @strCommand='DEL '+@strFileName+'.dat'

EXEC master..xp_cmdshell @strCommand

END