SQL Server2005自动备份的三种方法
由于公司大部分项目都是使用这种类型的数据库,于是先研究了下其备份方法。总结有如下三种:
1. 完全使用SQLServer自带的自动备份机制。在这种方式中,通过维护计划调用SQL Server内置的“备份数据库”任务并配置定时作业实现。
2. 不使用维护计划,在定时作业中配置备份命令。
3. 不使用维护计划,在master库中编写备份的存储过程,然后配置定时作业调用该存储过程。
下面以SQLServer2005上的操作为例进行说明。
1. 操作前提
要使SQLServer中的定时作业能够正确运行,必须先启动SQL Server代理。SQL Server代理主要提供周期性任务、服务器异常时给sa发送问题告警。详细的解释可参考如下链接:http://msdn.microsoft.com/en-us/library/aa174509(v=sql.80).aspx。
SQLServer的启动方法如下:
1. 在windows下选择“开始”-》“控制面板”-》“管理工具”-》“服务”,或是选择“开始”-》“运行”,在cmd窗口键入“services.msc”,进入到“服务”界面。
2. 找到SQL ServerAgent服务,启动该服务,并将启动类型配置为“自动”。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/19cab9d61b73629a066bce99f134bcc3.jpeg)
2. 使用自动备份机制
使用维护计划向导来配置维护计划。操作步骤如下:
1. 使用SQL Server ManagementStudio打开服务器,选择“管理”-》“维护计划”-》“维护计划向导”。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/de90e6d1aded13032cc79292e89e1c0a.jpeg)
2. 维护计划向导启动界面中列出了维护计划的主要作用,其中就包含数据库备份。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/4924fb37f060b393e89b8fd108cbf50d.jpeg)
3. 设置维护计划名称。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/494884b7fd93d74adc9d5b4e86995222.jpeg)
4. 选择“维护任务”。在这里可以根据指定的备份策略选择全量备份还是增量备份。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/77875440ac73189712a0dba77dd3a446.jpeg)
5. 选择维护任务顺序。如果上一步选择多个维护任务,则需要在此设置各个维护任务的顺序。
6. 配置维护任务。选择待备份的数据库及备份文件存放路径。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/b4a7b07eb2abc0ae8270d83719116a35.jpeg)
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/ccb718881e960eb484759862a3e105e6.jpeg)
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/957aad0fe9e2acd19abc18f41fc6ca39.jpeg)
7. 定义维护计划属性。在“选择维护计划属性”窗口,点击“更改”进行设置。这里主要设置维护计划对应的定时作业。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/8bc36f9e9261c043e0f37f175a830269.jpeg)
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/a1e164ad9ac97623aed902a7ef9794b2.jpeg)
8. 设置维护操作报告选项。维护操作报告可以txt形式存放在数据库运行目录下,也通过邮件发给相关人员。请根据实际需要配置。这里选择默认值。
9. 完成维护计划的创建。显示如下界面表明维护计划创建成功。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/4e85a4c28521da271dfa660c841b50de.jpeg)
10. 结果检查。在“管理”-》“维护计划”中可看到新建的维护计划,同时在“SQL Server代理”-》“作业”中也可看到同名的定时作业。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/342b03d90ec22a06a639911795ce16e6.jpeg)
双击新建的维护计划,查看其属性,可看到维护计划调用了SQL Server自带的“备份数据库”任务。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/ae88b8554090653f2ff979aeecc4d5b4.jpeg)
3. 使用备份命令
使用用户自定义的备份命令时,只需要在配置定时作业时增加备份命令,即可实现自动备份。具体的操作方法如下:
1. 使用SQL Server ManagementStudio打开服务器,选择“SQL Server代理”-》“作业”,右键选择“新建作业”。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/65fd327d529d2e46f75ef20457bf8b72.jpeg)
2. 在“新建作业”窗口中,选择“常规”页,设置作业名称。其他属性默认。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/1d8ee6249ae16e26f5d1f86a98b7fdd4.jpeg)
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 WITHNOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
蓝色部分F:\DB_Bak\表示备份文件的存放目录,DB_Bak表示备份文件以DB_Bak开头。备份文件的格式为“DB_Bakyyyy-mm-dd hh-mi-ss.bak”。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/e24caf07dacb067b2a09ac9845319338.jpeg)
4. 选择“计划”页,点击“新建”,设置作业计划。设置计划的名称、类型、执行频率、执行间隔及开始结束时间。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/47f7f6ebd26ca74500072d0f6f823145.jpeg)
5. 设置完成后,点击“确定”。其他页可不设置。
6. 检查结果。在“SQLServer代理”-》“作业”下,可看到新建的作业。选中作业,右键选择“属性”,可查看该作业对应的各项设置。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/122b62cfeccd5fd326d147fc03ebf084.jpeg)
4. 使用存储过程
如果需要在同一服务器上备份多个数据库,可选择使用存储过程进行自动备份。具体的操作步骤如下:
1. 使用SQL Server ManagementStudio打开服务器,选择“数据库”-》“系统数据库”-》“master”-》“可编程性”-》“存储过程”,右键选择“新建存储过程”。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/77560115b12fe444178e5a2275d149fe.jpeg)
2. 在存储过程创建窗口中,输入如下内容:
USE [master]
GO
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
-- Batch submitted through debugger:SQLQuery1.sql|0|0|C:\Users\Administrator\AppData\Local\Temp\2\~vsA9AA.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 ('BACKUPDATABASE test1 TO DISK='''+@strFileName+'.dat''')
SET @strCommand='HaoZipCa -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
--备份test2数据库
SET @strFileName ='F:\DB_Bak\test2_bak_'+@strDate;
EXEC ('BACKUP DATABASEtest2 TO 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
以上代码中,粗体部分[dbo].[BackupYLData]表示存储过程名为BackupYLData,F:\DB_Bak\test1_bak_表示备份文件存放在F:\DB_Bak目录下,以test1_bak_开头,test1表示需要备份的数据库名,HaoZipC a -t7z表示使用压缩工具将得到的文件压缩为.7z文件。请根据实际情况修改浅蓝色部分。
3. 存储过程修改完成后,点击工具栏上的“
”将存储过程编译刷新到数据库中。
4. 选择“数据库”-》“系统数据库”-》“master”-》“可编程性”-》“存储过程”,右键选择“刷新”,即可看到新增加的存储过程。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/d1941195e74ed9496d424cf7086d02b9.jpeg)
5. 新建作业。在“常规”页输入作业名称。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/e7b5096a8b577225b34d5f339adde3ea.jpeg)
6. 在“步骤”页,选择“新建”,输入执行步骤。注意类型必须选择“Transact-SQL脚本(T-SQL)”,数据库必须选择master(因为备份的存储过程在master中),在命令窗口中输入执行存储过程的命令行。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/b01329335672f4e4dcde921c9507675d.jpeg)
7. 在“计划”页,点击“新建”,设置作业计划。
![SQL <wbr>Server2005自动备份的三种方法 SQL <wbr>Server2005自动备份的三种方法](https://i-blog.csdnimg.cn/blog_migrate/4bcca8b74699feacb24ba01417697741.jpeg)
8. 点击“确定”,完成作业的设置。
5. 其他说明
自动备份作业创建完成后,可在设定的时间点后检查作业是否成功执行:
1. 查看备份目录下是否有文件生成,文件的命名是否符合需求;
2. 在“作业”中,选择相应的作用,右键选择“查看历史记录”,将会显示作业的执行结果,包括作业执行成功/失败、作业开始执行的时间、作业耗费的时间。