SQL Server2005异地自动备份方法（sql数据库）墙裂推荐

最近公司刚刚搭建了一台安防综合平台，综合平台服务器上安装了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设置->安全设置->本地策略->安全选项->网络访问：本地账户的共享和安全模式->经典-本地用户以自己的身份验证。