SQL SERVER 2008异地备份的代码

use master

declare @strdirname varchar(100)

declare @strdirname2 varchar(100)

declare @strcmd varchar(100)

declare @filemulu varchar(100)

declare @strsql varchar(100)

declare @strcmd2 varchar(100)

declare @strcmd3 varchar(100)

declare @strcmd4 varchar(100)

exec master..xp_cmdshell 'net use  \\192.168.0.10\beifen  admin   /user:admin'

set @strdirname=replace(substring(convert(varchar(20),(getdate()),120),1,10),'-','')+'06'

set @strcmd='md E:\backup\'

set @strcmd=@strcmd+@strdirname  

select @strcmd

exec master..xp_cmdshell @strcmd  

---1、建目录

set @filemulu='E:\backup\'+@strdirname+'\'

select @filemulu

-- 建目录

declare @filename varchar(100)

select @filename=@filemulu +'flgl_'+replace(replace(replace(convert(char(19),GETDATE(),21),'-',''),':',''),' ','')+'.bak'

select  @filename

backup database flgl to disk=@filename

select @filename=@filemulu +'middle_'+replace(replace(replace(convert(char(19),GETDATE(),21),'-',''),':',''),' ','')+'.bak'

select  @filename

backup database middle to disk=@filename

select @filename=@filemulu +'mt1223_'+replace(replace(replace(convert(char(19),GETDATE(),21),'-',''),':',''),' ','')+'.bak'

select  @filename

backup database mt1223 to disk=@filename

select @filename=@filemulu +'pmnetwork_'+replace(replace(replace(convert(char(19),GETDATE(),21),'-',''),':',''),' ','')+'.bak'

select  @filename

backup database pmnetwork to disk=@filename

---四个数据库都备份

set @strcmd2='md  \\192.168.0.10\beifen\'

set @strcmd2=@strcmd2+@strdirname  

select @strcmd2

set @strcmd3='copy '+@filemulu+'*.*'+ '   \\192.168.0.10\beifen\'+ @strdirname  

select @strcmd3

exec master..xp_cmdshell @strcmd3  

set @strdirname2=replace(substring(convert(varchar(20),(getdate()-3),120),1,10),'-','')+'06'

set @strcmd4='delete e:\backup\'+@strdirname2+'\*.*'

select @strcmd4

exec master..xp_cmdshell @strcmd4