RiCo技術農場

之前我寫過一篇 [SQL SERVER]使用SSIS執行自動還原資料庫，這一篇將使用TSQL來產生還原資料庫指令碼

備份檔案格式: 資料庫名稱_備份類型_yyyyMMddHHmm.bak

需開啟xp_cmdshell

USE Master; 
GO 
EXEC sp_configure 'show advanced options', 1;  
GO   
RECONFIGURE WITH OVERRIDE 
GO    
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE WITH OVERRIDE 
GO 

來源備份檔案

mydb_diff_201510050400.bak
mydb_full_201510050000.bak
mydb_log_201510050100.bak
mydb_log_201510050130.bak
mydb_log_201510050200.bak
mydb_log_201510050230.bak
mydb_log_201510050430.bak
mydb_log_201510050500.bak

 

--USE Master; 
--GO 
--EXEC sp_configure 'show advanced options', 1;  
--GO   
--RECONFIGURE WITH OVERRIDE 
--GO    
--EXEC sp_configure 'xp_cmdshell', 1;  
--GO  
--RECONFIGURE WITH OVERRIDE 
--GO   
 
--set NOCOUNT ON 

declare @backupPath nvarchar(500) --來源備份路徑 
declare @cmd varchar(8000)
declare @lastBackupdate varchar(16) 
declare @lastBackupfile nvarchar(300) 
declare @backupFile nvarchar(300) 
declare @dbName varchar(30) 
declare @fileList table (backupFile nvarchar(300) ,backupdate as right(backupFile,16)  ) 

set @dbName = 'mydb' --使用者資料庫名稱
set @backupPath = 'D:\sqlbk\' 

-- 取得檔案清單
set @cmd = 'DIR /b "' + @backupPath + '"'
insert into @fileList(backupFile) 
exec master.sys.xp_cmdshell @cmd 
--select * from @fileList

--取得最新完整備份檔案
select top 1 @lastBackupdate = backupdate,@lastBackupfile= backupFile 
from @fileList  
where backupFile LIKE '%.bak'  
AND backupFile LIKE @dbName + N'_full_%' 
order by backupdate desc

set @cmd = 'RESTORE DATABASE [' + @dbName + '] from DISK = '''  
       + @backupPath + @lastBackupfile + ''' WITH NORECOVERY, REPLACE' 
print @cmd 

--取得最新差異備份檔案
select top 1 @lastBackupdate = backupdate , @lastBackupfile=backupFile
from @fileList  
where backupFile LIKE '%.bak' and  backupFile like @dbName +N'_diff_%' 
AND backupdate > @lastBackupdate
order by backupdate desc

if @lastBackupfile IS NOT NULL 
begin 
   set @cmd = 'RESTORE DATABASE [' + @dbName + '] from DISK = '''  
       + @backupPath + @lastBackupfile + ''' WITH NORECOVERY' 
   --print @cmd 
   set @lastBackupdate = @lastBackupdate 
end

--取得最新交易備份檔案
select @cmd+=CHAR(13)+CHAR(10) +'RESTORE LOG ['+  @dbName + '] from DISK = ''' +@backupPath+backupFile + ''' WITH NORECOVERY '
   from @fileList 
   where backupFile like '%.bak'  and  backupFile like @dbName + N'_log_%' 
   AND backupdate > @lastBackupdate
   --FOR XML PATH('')  
 print @cmd   

set @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
print @cmd 

指令碼結果

RESTORE DATABASE [mydb] from DISK = 'D:\sqlbk\mydb_full_201510050000.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE [mydb] from DISK = 'D:\sqlbk\mydb_diff_201510050400.bak' WITH NORECOVERY
RESTORE LOG [mydb] from DISK = 'D:\sqlbk\mydb_log_201510050430.bak' WITH NORECOVERY 
RESTORE LOG [mydb] from DISK = 'D:\sqlbk\mydb_log_201510050500.bak' WITH NORECOVERY 
RESTORE DATABASE [mydb] WITH RECOVERY