SQL数据库的异地备份

sql server 代理

  作业－－》右击－－》新建作业－－》新建作业步骤－－》选择T-SQL类型输入如下脚本：

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='PowerPIP'
set @User='administrator'
set @Pwd='Cnooc2007'
set @IPPart='F$\PIPbackup'
set @IP='172.16.0.241'
set @retaindays=7

set @cmd='net use x: \\'+@IP+'\'+@IPPart+' '+@Pwd+' /user:'+@IP +'\'+ @User

exec master..xp_cmdshell @cmd

set @now=getdate()

set @i=0
while (@i<30)
begin
    set @deletefiles='x:\*'+convert(varchar(8),dateadd(dd,-@retaindays-@i,@now),112)+'*.*'
    set @cmd='del ' +@deletefiles
    exec master..xp_cmdshell @cmd
    set @i=@i+1
end

set @backupfile='x:\data'+@Store+'db'+
    replace(replace(replace(convert(varchar,getdate(),20),'-',"),' ',"),':',")+'.BAK'
set @sql='backup database data' +@Store +' to disk="'+@backupfile+"' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)

set @backupfile='x:\data'+@Store+'_tlog_'+
    replace(replace(replace(convert(varchar,getdate(),20),'-',"),' ',"),':',")+'.TRN'
set @sql='backup LOG data'+@Store+' TO DISK="'+@backupfile+"' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql)


exec master..xp_cmdshell 'net use x: /delete'