DB Backup copy +Restore DB

 

//backup DB

  SET @path = 'D:\DBA\Migration\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name NOT IN ('model','tempdb')  

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   

BEGIN   

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  

       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

END   

CLOSE db_cursor   

DEALLOCATE db_cursor 

DB Backup copy +Restore DB (估計時間)

作者:

DECLARE @SQL VARCHAR(1000) 

DECLARE @SQL1 VARCHAR(1000)

DECLARE @cmd NVARCHAR(500) 

DECLARE @dbName sysname 

DECLARE @backupFile NVARCHAR(500) 

DECLARE @backupPath NVARCHAR(500) 

DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

DECLARE @lastFullBackup NVARCHAR(500) 

SET @backupPath = 'O:\SQL_Backup\'

SET @cmd = 'DIR /b  ' + @backupPath 

   

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR 

   SELECT [name] 

   FROM master..sysdatabases

   WHERE [name]  IN ('TPCC','QSM','QIS','PM_Info','pda_patrol','HQ_Group','HPIPC','CSRC','pda_patrol_RMC')

   ORDER BY [name]

    

OPEN curDB 

FETCH NEXT FROM curDB INTO @dbName 

WHILE @@FETCH_STATUS = 0 

   BEGIN 

      INSERT INTO @fileList(backupFile) 

      EXEC master.sys.xp_cmdshell @cmd 

       SELECT @lastFullBackup = min(backupFile) 

       FROM @fileList  

       WHERE backupFile LIKE '%.bak'  

       AND backupFile LIKE +'%'+@dbName + '%' 

       

       PRINT @lastFullBackup

       --ALTER DATABASE @DB SET COMPATIBILITY_LEVEL = 100

       --SELECT @cmd = 'USE [' + @dbName +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13) 

       SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''   + @backupPath + @lastFullBackup + ''' WITH  NOUNLOAD,RECOVERY, REPLACE, STATS=10' 

       

       

      /** SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  

       + @backupPath + @lastFullBackup + ''' 

 WITH  

  MOVE ''QIS'' TO ''D:\QIS_SQL\QIS.mdf'',

  MOVE ''QIS_log'' TO ''D:\QIS_SQL\QIS.ldf'',

 RECOVERY, REPLACE, STATS=10' **/

                      

       PRINT (@cmd)

         --select min(backupFile) from @fileList WHERE backupFile LIKE '%.bak' AND backupFile LIKE +'%'+@dbName + '%'  ;

       exec (@cmd)

       --PRINT @SQL1

       FETCH NEXT FROM curDB INTO @dbName 

   END 

   

  

   

CLOSE curDB 

DEALLOCATE curDB