存储过程异地备份数据库

CREATE procedure ermback
as
Declare @strPsw varchar(50)
Declare @strUsr varchar(50)
Declare @strCmdShell varchar(300)
Declare @strDataBaseName varchar(20)
Declare @FullFileName Varchar(200)
Declare @FileIp varchar(50)
Declare @FilePath varchar(50)
Declare @Hostname varchar(50)
Declare @FileFlag varchar(50)
Declare @File1 varchar(50)
Declare @Disk varchar(10)
Declare @ToFileName varchar(200)
Declare @SQLStr varchar(500)
Declare @FlagDel varchar(20)
declare   @FileTime datetime
Set @strDataBaseName='erm'
Set  @FilePath='d:\ermbak\'
Set @FileIp='150.38.116.8'  
Set @Hostname='administrator'
Set @strPsw='xhfy4190132'
Set @Disk='d'  
Set @File1='ermbak'
Set  @FileTime=CONVERT(datetime,CONVERT(varchar(8),DATEPART(year,getdate()))+'-'+CONVERT(varchar(8),DATEPART(m,getdate())-1)+'-'+CONVERT(varchar(8),DATEPART(d,getdate())))
Set @FileFlag=convert(varchar(10) ,datepart(yy,getdate()))+convert(varchar(10) ,datepart(mm,getdate()))+convert(varchar(10) ,datepart(dd,getdate()))+convert(varchar(10) ,datepart(hh,getdate()))+convert(varchar(10) ,datepart(mi,getdate()))+convert(varchar(10) ,datepart(ss,getdate()))
Set @strUsr=@FileIp+'\'+@Hostname  
Set @strCmdShell= 'net use \\'+@FileIp+'\'+@Disk+'$ ' + @strPsw + ' /user:' +@strUsr   
Set @FullFileName=@FilePath+'erm_backup_'+@FileFlag+'.BAK'  
Set @ToFileName='\\'+@FileIp+'\'+@Disk+'$\'+@File1
Set @FlagDel='true'
Set @SQLStr='copy '+@FullFileName+' '+@ToFileName
dump database @strDataBaseName To  @FullFileName
exec master..xp_cmdshell @strCmdShell
exec Master..xp_cmdshell @SQLStr
if (@FlagDel ='True')
 EXECUTE master.dbo.xp_delete_file 0,@FilePath, @FileTime
 EXECUTE master.dbo.xp_delete_file 0,@ToFileName, @FileTime

go

sp_configure "xp_cmdshell context",0

删除备份文件

CREATE procedure delermback
as
Declare @strPsw varchar(50)
Declare @strUsr varchar(50)
Declare @strCmdShell varchar(300)
Declare @FileIp varchar(50)
Declare @FilePath varchar(50)
Declare @Hostname varchar(50)
Declare @File1 varchar(50)
Declare @Disk varchar(10)
Declare @ToFileName varchar(200)
 
declare   @FileTime datetime
Set  @FilePath='d:\ermbak\'
Set @FileIp='150.38.116.8'  
Set @Hostname='administrator'
Set @strPsw='xhfy4190132'
Set @Disk='d'  
Set @File1='ermbak'
Set  @FileTime=CONVERT(datetime,CONVERT(varchar(8),DATEPART(year,getdate()))+'-'+CONVERT(varchar(8),DATEPART(m,getdate())-1)+'-'+CONVERT(varchar(8),DATEPART(d,getdate())))
Set @strUsr=@FileIp+'\'+@Hostname  
Set @strCmdShell= 'net use \\'+@FileIp+'\'+@Disk+'$ ' + @strPsw + ' /user:' +@strUsr   
Set @ToFileName='\\'+@FileIp+'\'+@Disk+'$\'+@File1
Set @FlagDel='true'

if (@FlagDel ='True')
 exec master..xp_cmdshell @strCmdShell
 EXECUTE master.dbo.xp_delete_file 0,@FilePath, @FileTime
 EXECUTE master.dbo.xp_delete_file 0,@ToFileName, @FileTime

go

转载于:https://www.cnblogs.com/hhjwqh/p/8285062.html