最近公司刚刚搭建了一台安防综合平台,综合平台服务器上安装了SQL Server 2005,由于没有备份服务器,所以只能异地备份数据库了。在网上找了很多的资料,自己整合了一下 。
一、开启SQLAgent服务,并设置为“自动”
二、备份
1、SQL代理-作业-新建作业,这是在SQL2005下的操作,其实SQL2000也是大同小异(管理→SQLServer代理→右键作业→新建作业)
图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设置->安全设置->本地策略->安全选项->网络访问:本地账户的共享和安全模式->经典-本地用户以自己的身份验证。