SQL SERVER 2005如何建立自动备份的维护计划

SQL数据自动备份与删除
解决思路：在2005 SQL环境下，进行创建自动备份与自动删除。

解决方案：
1、点击”管理”->选中”维护计划”->右键点击->选中”新建维护计划(N)”;如下两图：
2、点击”新建维护计划”之后弹出如下图的对话框,给维护名称起名字;再点击”确定”,如下图：
3、从左下边工具箱中拖出”备份数据库任务” 到右边的空白处;然后点击右键选择”编辑”，如下图：
4、选择”编辑”之后弹出如下图的对话框;选择你需要备份的数据库。

5、设置好备份的路径，点击确定，任务创建完成。

接下来创建改任务的计划。

执行时间可以设为每月、每周、每天,根据相关情况选择适合的时间!（因为备份会占用服务器资源，所以建议选择在下班时间。

一般情况下，我们会定义为0点以后。

）
6、检查一下你的设置,打开所备份的文件夹看是否有备份文件。

7、创建删除备份文件
1)此处添加的任务为清除历史记录任务
2) 编辑该任务
3) 设定超过四周时间的备份文件自动删除。

SQL2005自动定时备份数据库并按日期命名SQL2005的维护计划里面自带了备份数据库任务，但不会自动按日期命名，不方便，下面的方法是以存储过程来解决的。

要用SQL2005的维护计划功能首先要确认SQL Server Agent服务是在启动状态，然后打开SQL Server Management Studio，展开对象资源管理器里的“管理”，右击维护计划选择新建维护计划，为维护计划命名，如：TestDB_Backup，选择默认的子计划“Subplan_1 ”并点击上方的子计划设置按钮来设置子计划执行方式和时间：出现设置界面，设置每天3点执行一次，如图确定后保存一下，然后新建一个查询页，开始创建存储过程，代码如下：USE [TestDB]02.GO03.SET ANSI_NULLS ON04.GO05.SET QUOTED_IDENTIFIER ON06.GO07.CREATE PROCEDURE[BackupDatabase](@FolderPath varchar(500))08.as09.DECLARE@FullPath varchar(1000)10.set@FullPath = @FolderPath+'TestDB_'+convert(VARCHAR(4),year(getdate()))+right( '0'+convert(VARC HAR(2),MONTH(getdate())),2)+right('0'+convert(VARCHAR(2),DAY(getdate( ))),2)+ '.bak'11.backup database[TestDB] to disk=@FullPath WITH INIT12.return设置完毕，切换回维护计划设置页面，在左边的工具箱中拖入一个“执行T-SQL语句任务”：双击拖入后的“执行T-SQL语句任务”，输入执行存储过程的语句来完成每天的自动备份工作，代码如下：view sourceprint?e [TestDB]2.exec BackupDatabase 'E:\SqlData\TestDB\'确定后保存该维护计划即可。

一、备份数据库维护计划方案【注】：1、先启动SQL Server Agent服务2.、事务历史记录备份(只限于完整和大容量日志恢复模式)。

3、下面中”清除备份trn文件” & “清除备份日志文件” 没有做计划说明二、通过维护计划精灵实现备份计划【注】：当前备份计划里面包含有多个不同的排程，这里选择的是“对每一工作的个别排程”选项。

【注】：1、“数据库”位置选择“所有用户数据库”2、排程位置，选择每天PM 01:30执行一次完整备份【注】：1、“数据库”位置选择“所有用户数据库”2、排程位置，选择每天每2小时执行一次差异备份【注】：1、“数据库”位置选择“所有用户数据库”2、排程位置，选择每天每30分钟执行一次事务历史记录备份【注】：1、“文件夹”下边位置，勾选“包含第一次的子文件夹”2、排程位置，选择每天每30分钟执行一次事务历史记录备份完成备份计划的设置，就可以在“维护计划”列表中找到刚建立的备份计划，同时在“作业”列表中找到对应的作业。

生成的各个作业，在”备份数据库计划”中是作为子计划来描述。

可以通过修改维护计划选项来，修改各个子计划的内容(也可以通过作业的内容来修改)：在这里，可以通过修改各个子计划的内容，可以从最左边的工具箱中拖动相应的工具添加到右边的计划中。

这里把4个子计划名称改下，让它描述的更加清晰：三、测试备份数据库作业当一个维护计划中只包含有一个子计划，可以通过维护计划右键菜单中的[执行]来检查测试：当一个维护计划中只包含有多个子计划，通过右键菜单[执行]按钮，就会报错：这里，'备份数据库计划' 维护计划包含多个子计划，可以在对象总管的[SQL Server Agent] 节点下选取它们的关联作业，用这种方式个别执行这些计划，达到调试的目的。

作业执行成功后，我们就可以在路径”E:\DBBackup”下看到已经备份的数据库数据：每一个数据库，都会生成一个对应名称的文件夹;完成备份时,同时会生成一个备份报表文件(txt格式)：使用手动启动完全备份作业的方法，来启动差异、交易备份作业。

SQL Server 2005本地备份与远程服务器备份假设我们的数据库不是很大，但是我们的数据会比较重要，大概一天一个备份就能满足我们的业务需要，那我们应该如何设计我们的备份方案呢？星期天晚上02:00做一次数据库的完全备份，其它时间星期一至星期六晚上02:00做一次差异备份，基本逻辑如下图所示：（图：数据库备份文件生成逻辑）SQL Server 2005本地备份1.数据库 -> 【管理】-> 【维护计划】 -> 【新建维护计划】；（图1）2.勾选【备份数据库(完整)】和【备份数据库(差异)】；（图2）3.设置完整备份文件的路径和备份文件的扩展名；注意特定数据库的选择；（图3）4.填写完整备份作业计划的名称，执行间隔勾选星期日，设置执行时间；（图4）5.设置差异备份文件的路径和备份文件的扩展名；注意特定数据库的选择；（图5）6.填写差异备份作业计划的名称，执行间隔勾选除星期日之外的日期，设置执行时间；（图6）7.这就生成完整备份与差异备份的子计划了，（图7）8.在完整备份的设计界面中，从左边拖拉一个【清除历史记录】的控件到右边，并双击进行下图的设置；（图8）9.从左边拖拉一个【清除维护】的控件到右边，并双击进行下图的设置，这里需要设置完整备份的路径和文件的扩展名；（图9）10.在步骤上加上箭头形成完全备份作业的流程图；（图10）11.在差异备份的设计界面中，从左边拖拉一个【清除历史记录】的控件到右边，并双击进行下图的设置；（图11）12.从左边拖拉一个【清除维护】的控件到右边，并双击进行下图的设置，这里需要设置差异备份的路径和文件的扩展名；（图12）13.在步骤上加上箭头形成差异备份作业的流程图；（图13）14.在维护计划下生成Ant_Backup，在代理中生成完整备份与差异备份的作业；（图14）SQL Server 2005本地备份与远程服务器备份有时我们需要在联网的服务器之间交叉备份，比如A是数据库服务器，B是网络上与之相连的另外一台服务器，需要将A上的数据库自动备份到B上，这时就需要考虑远程备份，备份的原理即：添加网络驱动器映射，将备份文件备份到远程目标，最后删除网络驱动器映射。

之前数据库备份都是做在本地其他磁盘或者磁盘阵列上，但是一旦感染病毒，这都是扯淡。

所以，我们应该将数据库的备份放在其他服务器上。

据此操作方法如下：打开Microsoft SQL Server Management Studio ，点击菜单“管理”---“维护计划”---创建维护计划--对计划进行命名（这里我们叫bak），命名后，在“工具箱”中找到“执行T-SQl语句任务”，并进行双击，此时，右边空白处会弹出一个提示框，双击提示框输入以下SQL语句块：EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'xp_cmdshell', 1RECONFIGUREdeclare @strdirnameD varchar(100)declare @strdirnameT varchar(100)declare @RemotePathstr varchar(100)declare @LocalPathstr varchar(100)declare @DBName varchar(50)set @strdirnameD=replace(substring(convert(varchar(20),getdate(),120),1,10),'-','')set @strdirnameT=replace(substring(convert(varchar(20),getdate(),120),12,8),':','')set @RemotePathstr = '\\172.16.10.12\dbbackup67\poweron'+@strdirnameD+@strdirnameT+'.bak' //这里指定了网络服务器的位置，以及名称set @DBName='poweron' //本地数据库的名称exec master..xp_cmdshell 'net use z: \\172.16.10.12\dbbackup67$ "kuanyu.oucsei"/user:172.16.10.12\Administrator'backup database @DBName to disk=@RemotePathstrexec master..xp_cmdshell 'net use z: /delete'编辑完成后，点击“确定”；我们可以对编辑框进行重名名（右击--重命名），此时，我们可以右击“维护计划”中的bak，单击“执行”但是，此时，我们会看到执行失败的提示，通过查看日志，发现是远程的用户名、密码不对重点：解决方法如下：!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!打开“SQL Server Configuration Manager”找到SQL server 配置管理器（本地）---SQL Server2005 服务--分别右击“SQL Server （MSSQLSERVER）和SQL ServerAgent（MSSQLSERVER）”，右击“属性”，选择“登录”选项卡，将登录身份修改为“本账户”，然后输入账户名、密码和确认密码（账户名和密码都是计算机的用户名密码）；输入如下：需要注意的是：本地数据库的用户名、密码必须与远程备份服务器的用户名密码相同。

Sqlserver2000 设置方法
1 打开企业管理器
2 选择要备份的数据库，右键—》维护计划
这里可以选择“更改”设置调度时间，即工作频率
这里最好选择每天都备份，备份的时间选择在晚上访问量少的时间段。

备份文件的路径可以选择默认。

也可以自己配置一个路径；为每个数据库创建子目录，可选，可不选；
删除早期文件，可选可不选
3 完成上面的步骤之后，找到管理—》SQL Server 代理—》作业就会看到右边的作业，
Sqlserver2005
打开企业管理器：管理—》维护计划
右键- 维护计划向导【也可以新键维护计划，然后在设置】
这里选择数据库备份任务，可以选择差异备份，也可以选择完全备份；如果是选择差异备份，记得每次备份都不能丢掉，如果是完全备份你可以随便撒呀了，只要最后一次备份存在。

下一步–》下一步
选择数据库---》
这里选择每天都做备份
完成之后，在管理—》维护计划里会显示刚添加的计划；在作业里会出现相同名称的作业任务。

到设置的时间点，系统会自动备份数据库，生成如下文件【*.bak】
修改设计或者查看设置信息
在作业-→选中作业名称，双击或者右键找到属性，可以打开上面添加的维护计划。

如下
选中→常规、步骤、计划都可以弹出来具体的设置信息，也可以重新编辑。

SQL SERVER 2005自动备份数据库和删除备份SQL SERVER 2005里自动备份数据库和删除旧有备份的办法打开SQL SERVER MANAGEMENT STUDIO，启动SQL SERVER代理服务（注意在“控制面板-管理工具-服务”中设置SQL SERVER AGENT的启动类型为自动）。

启动后点击“作业-新建作业”，弹出一个作业属性的窗口，在“常规”栏目打开SQL SERVER MANAGEMENT STUDIO，启动SQL SERVER代理服务（注意在“控制面板-管理工具-服务”中设置SQL SERVER AGENT的启动类型为自动）。

启动后点击“作业-新建作业”，弹出一个作业属性的窗口，在“常规”栏目里可以先给作业命名，假设为“备份数据”。

一、备份数据的第一个步骤--“备份当日数据”在“步骤”栏目里新建一个步骤名为“备份当日数据”，类型为“T-SQL”，数据库选择你要操作的数据库（假设为“TESTDB”），命令窗口里填入备份的SQL语句。

假设备份数据放在“D:\BACKUP”里，备份文件命名格式为“SQL-2009-3-26.BAK”，那么语句如下：DECLARE @filename V ARCHAR(255)DECLARE @date DATETIMESELECT @date=GETDA TE()SELECT @filename = 'D:\BACKUP\SQL-'+CAST(DATEPART(yyyy,@date) as varchar)+'-'+CAST(DATEPART(mm,@date) as varchar)+'-'+CAST(DA TEPART(dd,@date) as varchar)+'.bak'BACKUP DATABASE [TESTDB] TO DISK = @filename WITH INITGO在步骤属性的高级的“成功时要执行的操作”选择“转到下一步”即可，这样“备份当日数据”的步骤已经建立好。

这两天在帮客户研究SQL2005的数据库同步，但尝试过第三方软件和自带的同步和镜像都失败后就想到了用这个笨方法来实现，不管怎么说总算完成任务了。

主机端设置方法：把以下代码存成BAT文件通过计划任务按排每天凌晨两点执行！ stop MSSQLSERVER /y2."C:\Program Files\WinRAR\Rar.exe" a -k -r -s -m1 -inul"E:\BAKUP\SQLData\SJQY%date:~0,10%.RAR" "E:\db"3.xcopy "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\hz65.ldf""E:\BAKUP\SQLData\HZ65" /e /i /q4.xcopy "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\hz65.mdf""E:\BAKUP\SQLData\HZ65" /e /i /q start MSSQLSERVER start SQLSERVERAGENT7."C:\Program Files\WinRAR\Rar.exe" a -k -r -s -m1 -inul"E:\BAKUP\SQLData\HZ65%date:~0,10%.RAR" "E:\BAKUP\SQLData\HZ65"8.del "E:\BAKUP\SQLData\HZ65\*.*" /s /q9."C:\Program Files\WinRAR\Rar.exe" a -r -s -m1 -o+ -inul"E:\BAKUP\SQLData\sjqy_backup.rar" "E:\BAKUP\SQLData\sjqy_backup.bak"10.del "E:\BAKUP\SQLData\sjqy_backup.bak" /s /q11."C:\Program Files\WinRAR\Rar.exe" a -r -s -m1 -o+ -inul"E:\BAKUP\SQLData\hz65_backup.rar" "E:\BAKUP\SQLData\hz65_backup.bak"12.del "E:\BAKUP\SQLData\hz65_backup.bak" /s /q以下代码是在数据库管理的维护计划内添加，每天凌晨一点开始自动备份！1.BACKUP DATABASE [sjqy] TO DISK = N'E:\BAKUP\SQLData\sjqy_backup.bak' WITHNOFORMAT, INIT, NAME = N'sjqy-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 102.GO3.declare @backupSetId as int4.select @backupSetId = position from msdb..backupset where database_name=N'sjqy'and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'sjqy' )5.if @backupSetId is null begin raiserror(N'验证失败。

Sql sever 2005 自动备份第一步：选择一个目标数据库，建立自动备份存储过程如下图所示：第二步：建立存储过程如下：USE [AutoBackDB]GO/****** 对象: StoredProcedure [dbo].[USP_BackupDatabase] 脚本日期: 12/29/2008 10:59:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[USP_BackupDatabase]@databaseName sysnameASBEGINSET NOCOUNT ON;DECLARE @sqlCommand NVARCHAR(1000)DECLARE @dateTime NVARCHAR(20)SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +' TO DISK = ''D:\AutoBackDB\' + @databaseName + @dateTime + '.BAK'''EXECUTE sp_executesql @sqlCommandEND执行此存储过程。

第三步：打开SQL代理服务如下图：第四步：新建代理作业如下图：第五步：设置作业步骤：execute USP_BackupDatabase（存储过程名） etccard（备份的数据库名）第六步：编辑备份计划如下图所示：编辑备份的时间。