批量备份和还原数据库

DECLARE @name        NVARCHAR(200)    
DECLARE @path        NVARCHAR(500)        
DECLARE @sql   NVARCHAR(500)    
DECLARE @bsql   VARCHAR(500)    
DECLARE @NowDay   VARCHAR(20)  --设置时间
  SET @NowDay=convert(VARCHAR(8),getdate(),112);
--备份文件存放路径  
SET @path = '\\192.168.60.177\bak\'     
      
DECLARE cursors CURSOR    
FOR  
    --查询集合  
    SELECT [name]  
    FROM   [sysdatabases]  
    WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb','ReportServerTempDB','ReportServer')  
                
 OPEN cursors                         
 FETCH NEXT FROM cursors INTO @name    
WHILE @@fetch_status = 0  
BEGIN  
    --遍历集合   
    SET @bsql=@path+@name+'_''+@NowDay +''.bak' +''''
    PRINT ' Set @bsql='''+@bsql+''
    SET @sql=  ' BACKUP DATABASE ['  + @name + '] TO DISK =  @bsql    WITH INIT,NOUNLOAD,NOSKIP,COMPRESSION,CHECKSUM,STATS=10,NOFORMAT'   
   PRINT @sql
   --EXEC(@sql)
   FETCH NEXT FROM cursors INTO @name 
END 
 CLOSE cursors             
 deallocate cursors

DECLARE @name        NVARCHAR(200)    
DECLARE @path        NVARCHAR(500)        
DECLARE @sql   NVARCHAR(500)    
DECLARE @bsql   VARCHAR(500)    
DECLARE @NowDay   VARCHAR(20)  --设置时间
  SET @NowDay=convert(VARCHAR(8),getdate(),112);
--备份文件存放路径  
SET @path = '\\192.168.60.177\bak\'     
      
DECLARE cursors CURSOR    
FOR  
    --查询集合  
    SELECT [name]  
    FROM   [sysdatabases]  
    WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb','ReportServerTempDB','ReportServer')  
                
 OPEN cursors                         
 FETCH NEXT FROM cursors INTO @name    
WHILE @@fetch_status = 0  
BEGIN  
    --遍历集合   
    SET @bsql=@path+@name+'_''+@NowDay +''.bak' +''''
    PRINT ' Set @bsql='''+@bsql+''
    SET @sql=  ' BACKUP DATABASE ['  + @name + '] TO DISK =  @bsql    WITH INIT,NOUNLOAD,NOSKIP,COMPRESSION,CHECKSUM,STATS=10,NOFORMAT'   
   PRINT @sql
   --EXEC(@sql)
   FETCH NEXT FROM cursors INTO @name 
END 
 CLOSE cursors             
 deallocate cursors