SQL2005自动镜像备份

  公司为提高数据的安全性，采用多媒介存储的方式备份，

自己写了一份公司备份策略，

/*自动备份过程  add at 2009-3-24*/
  
Declare @Load1 varchar(max),@Load2 varchar(max),@load3 varchar(max),@FileName varchar(200)
Declare @DBName varchar(100)     
/*备份路径设置*/
select @Load1='C:/backup/',@Load2='S:/backup/',@load3='T:/Backup/'      
 /*备份文件名称生成*/
      
select @FileName='_backup_'+cast(year(getdate()) as varchar(10))+right('00'+cast(month(getdate()) as varchar(10)),2)
          +right('00'+cast(day(getdate()) as varchar(10)),2)+right('00'+cast(datepart(hh,getdate())as varchar(10)),2)
          +right('00'+cast(datepart(MI,getdate())as varchar(10)),2)+'.bak'

Declare DBList cursor  for
select name from master.dbo.sysdatabases where  name not in ('master','model','msdb','tempdb','fabricstock')
 open DBList
fetch next from DBList into @DBName
  while @@fetch_status=0 
   begin        
exec('              
    begin try 
          backup database '+@DBName+'  to DISK='''+@load1+@DBName+@filename+ '''
          mirror to  DISK='''+@load2+@DBName+@filename+'''
          mirror to  DISK='''+@load3+@DBName+@filename+''' WITH FORMAT 
      end try
      begin catch
          print '''+@DBName+''''+'''备份失败''
      end catch   ')  
 
   fetch next from DBList into @DBName
 end 
    close DBList 
  deallocate DBList