ms SQL 异地备份

1、在备份服务器创建一个文件夹，左键属性

点击权限 给超级管理员授予全部权限

2、新建作业

新建步骤

 

其中命令如下：
--显示高级选项
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  @retaindays  int  
declare  @now  datetime  
declare  @deletefiles  varchar(2000)  
declare  @cmd  varchar(2000)  
declare  @DelBeforDay  int  
declare  @User  varchar(2000)  
declare  @Pwd  varchar(2000)  
declare  @Store  varchar(2000)  
declare  @IPPart  varchar(2000)  
declare  @IP  varchar(2000)    
set  @Store='basename'             --数据库名  
set  @User  ='administrator'       --用户名(异地服务器)-----要管理员权限  
set  @Pwd  ='administratorkey'     --密码（异地服务器）-------密码不要有符号  
set  @IPPart='备份'                --路径(异地服务器的共享目录，此目录要有有上面用户的访问读写权限)  
set  @IP='192.xx.xx.xx'            --异地服务器IP或主机名  
set  @retaindays=30                --要保留备份的天数(此时间内不可被SQL覆盖，但可被其他方式删除)
set  @DelBeforDay=-3               --删除DelBeforDay以前的数据
--将异地服务器的共享文件夹映射到本地Z盘
set @cmd='net  use  Z: \\'
+@IP+'\'
+@IPPart+' "'+@Pwd+'" /user:'+@IP+'\'+@User
  --创建映射  
exec master..xp_cmdshell @cmd
begin try
  --开始备份数据库
  --备份文件名：数据库名_日期（如20140710）.bak
  set  @backupfile='Z:\'+@Store+'_'+convert(varchar,getdate(),112)+'.BAK'        
  set  @sql='backup  database  '  +  @Store  +  '  to  disk='''+@backupfile+'''  with  retaindays='+convert(varchar(10),@retaindays)  
  exec  (@sql)
  --开始备份日志（需要的话开启，但是数据库模式不能为简单模式）
  --set  @backupfile='z:\'+  @Store  +'_tlog_'+  
  --        convert(varchar,getdate(),112)+'.TRN'  
  --set  @sql='backup  LOG  '  +  @Store  +  '  TO  DISK='''+@backupfile+'''  with  
  --retaindays='+convert(varchar(10),@retaindays)  
  --exec  (@sql)

  --删除历史备份数据（保留文件名最大的）
  set  @sql='dir /s /a-d /b Z:\*.bak'
  CREATE TABLE #DataFile([output] varchar(100) NULL )
  insert into #DataFile
  EXEC  master..xp_cmdshell @sql
  DECLARE DataFile CURSOR FOR
  select output from #DataFile where output<>(select max(output) from #DataFile)
  OPEN DataFile
  FETCH Next From DataFile into @Store
  While  @@Fetch_Status=0
  begin
    set @sql='del '+@Store
    exec  master..xp_cmdshell @sql
    FETCH Next From InterfaceFile into @Store
  end

 

end try
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() as ERROR_MESSAGE;
END CATCH    
--删除映射  
exec  master..xp_cmdshell'net  use  Z:  /delete'    
--关闭允许执行xp_cmdshell  
EXEC  sp_configure'xp_cmdshell',  0          
GO            
RECONFIGURE  WITH  OVERRIDE          
GO
新建作业

如果是win 2003 访问win 2008 需要在2003启动Computer Browser 服务。

可能会用到的命令

--添加映射
net use Z: \\10.81.74.10\异地备份 "jingqi!@#$%^xnh" /user:10.81.74.10\jqxnh
--备份文件
backup database hzyl_base to disk='Z:\hzyl_base_20170214.BAK' with retaindays=30

--删除映射
net use * /del /y