SQL Server2005异地自动备份方法
最近公司刚刚搭建了一台安防综合平台，综合平台服务器上安装了sql server 2005，由于没有备份服务器，所以只能异地备份数据库了。在网上找了很多的资料，自己整合了一下 。
一、开启sql agent服务，并设置为“自动”
二、备份
1、sql代理-作业-新建作业，这是在sql2005下的操作，其实sql2000也是大同小异（管理→sql server代理→右键作业→新建作业）
图1
2、在常规中输入名字
图2
3、“步骤”项—>新建，输入名称，选中数据，输入以下语句，
图3
语句如下：
--在sql server2005中cmdshell命令默认是关闭的，首先要开启cmdshell命令：
--显示高级选项（仅需执行一次）
exec sp_configure 'show advanced options', 1
go
reconfigure
go
--允许执行xp_cmdshell
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
declare @sql varchar(4000)
declare @backupfile varchar(2000)
declare @now datetime
declare @retaindays int
declare @deletefiles varchar(2000)
declare @cmd varchar(2000)
declare @i int
declare @user varchar(2000)
declare @pwd varchar(2000)
declare @store varchar(2000)
declare @ippart varchar(2000)
declare @ip varchar(2000)
-------------以下自行修改-----------
set @store='employeeinfo' --------数据库名-----------
set @user ='liuhs' ----------用户名----------要管理员权限
set @pwd ='winter' ----------密码----------密码不要有符号
set @ippart='d$\back' -----路径-----
set @ip='192.168.3.42' -----服务器ip或主机名--
set @retaindays=1 ---要保留备份的天数
------------以上自行修改------------
--添加映射驱动器
declare @string nvarchar(200)
set @string = 'net use z: \\'+@ip+'\'+@ippart+' "winter" /user:hp-liuhs\'+@user
exec master..xp_cmdshell @string
set @now=getdate()
---删除以前的备份，这个还没有实现好，有时间再修改了
set @i=0
while (@i < 30)
begin
set @deletefiles = 'z:\data'+@store+ '_db_' +convert(varchar(8),dateadd(dd,-@retaindays- @i,@now),112)++'.bak'
set @cmd='del ' + @deletefiles
exec master..xp_cmdshell @cmd
set @i = @i +1
end
----开始备份
set @backupfile='z:\data'+ @store + '_db_'+ replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak'
set @sql='backup database ' + @store + ' to disk='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)
set @backupfile='z:\data'+ @store +'_tlog_'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.trn'
set @sql='backup log ' + @store + ' to disk='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)
---删除映射
exec master..xp_cmdshell 'net use z: /delete'
4、新建作业计划，在指定时间内备份
图4
5、最后查询备份服务器是否备份成功
如果出现了“系统错误 5， 拒绝访问”：
首先要确定对备份服务器要有足够的权限，最好是“administrators”组账户。
其次 到备份服务器上，打开组策略->计算机配置->windows设置->安全设置->本地策略->安全选项->网络访问：本地账户的共享和安全模式->经典-本地用户以自己的身份验证。