批量備份和還原資料庫

zping發表於2018-02-05
資料庫
 
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

　　

相關文章