[스크립트] 특정경로 Full백업 일괄 복원

복구테스트 진행시 편리하게 하기 위해 작성한 스크립트로,
다수의 DB Full백업본에 대한 복원이 필요할 경우 사용하기 적합합니다.

 

복원 쿼리 순서

1. bak파일 리스트 검색
2. bak 파일 별 복원 (mdf/ldf/ndf) 정보 검출
3. DB (bak파일) 별 복원 스크립트 & 수행 bat 생성
4. bat 실행 & 로그 생성

 

쿼리문

• 입력 파라미터

  • @bakPath/@SQLData/@SQLPath/@LogPath/@batPath

 
--by JYOH. 20200103
  
declare @SQLPath varchar(400) , @batPath varchar(400), @LogPath varchar(400),
  @bakPath varchar(400) , @SQLData varchar(400)  
   
set @bakPath =  'D:\bak_user\' -- bak파일 위치 
set @SQLData ='D:\SQLData\'  -- 복원 경로
set @SQLPath = 'D:\Query\'  -- 복원 스크립트 생성 위치
set @LogPath = 'D:\Log\'  -- 복원 스크립트 수행 결과
set @batPath = 'D:\bat\'  -- 복원 스크립트 수행 bat 파일 위치

 
 exec sp_configure 'show advanced option' ,1
 reconfigure
 exec sp_configure 'xp_cmdshell' ,1
 reconfigure
  
  
 
if OBJECT_ID('tempdb..#tmpT1' ) is not null
 drop table #tmpT1
 CREATE TABLE #tmpT1( output varchar(200) NULL)
 
if OBJECT_ID('tempdb..#tmpT2' ) is not null
 drop table #tmpT2
 CREATE TABLE #tmpT2( [num] [int] IDENTITY(1,1) NOT NULL,DBName varchar(100) null, bakPath varchar(200) NULL)
 
if OBJECT_ID('tempdb..#tmpT3' ) is not null
  drop table #tmpT3
 CREATE TABLE #tmpT3(
      [LogicalName] [nvarchar] (128) null, [PhysicalName] [nvarchar](260) NULL, [Type] [char](1) NULL, FileGroupName nvarchar (128) null,
      size numeric(20,0) null, MaxSize numeric(20,0) null, Field bigint, CreateSN numeric(25,0) null,
      DropLSN numeric(25,0) null, UniqueID uniqueidentifier null, ReadOnlyLSN numeric(25,0) null, ReadWriteLSN numeric(25,0)null,
      BackupSizeInBytes bigint, SourceBlockSize int,FileGroupID int, LogGroupGUID uniqueidentifier null,
      DifferentialBaseLSN numeric(25,0) null, DifferentialBaseGUID uniqueidentifier null, IsReadOnly bit,IsPresent bit,TDETHumbprint varbinary(32)
      )
 
if OBJECT_ID('tempdb..#tmpT4' ) is not null
 drop table #tmpT4
 CREATE TABLE #tmpT4( stringQ varchar(max))
  
EXECUTE master.dbo.xp_create_subdir @SQLPath
EXECUTE master.dbo.xp_create_subdir @LogPath
EXECUTE master.dbo.xp_create_subdir @batPath
 
declare @FindSQL varchar(200), @SQL1 varchar(1000)
set @FindSQL =  'dir /b '+@bakPath
insert into #tmpT1 
 exec   master..xp_cmdshell @FindSQL
  
insert into #tmpT2 (DBName,bakPath)
 select LEFT(output,charindex('_backup_',output)-1),output from #tmpT1
   
declare @cnt int, @CreateSQL varchar(3999), @createBat varchar(400),
  @execBat varchar(100), @dbname varchar(100), @stringQ2 nvarchar(max)
select @cnt= num from #tmpT2
  
  
while (1=1)
begin
  
 if (@cnt=0 or @cnt is null)
 break
  
 truncate table #tmpT3
 select @SQL1 = N'restore filelistonly from disk = '''+@bakPath+bakPath+'''',@dbname= DBName
 from  #tmpT2
 where num = @cnt
  
 insert into #tmpT3
  exec(@sql1)
   
 select @stringQ2 = 'RESTORE DATABASE ['+@dbname+'] '+ 'from disk = '''+@bakPath+bakPath+'''' +' WITH  FILE = 1,'
 from #tmpT2
 where DBName = @dbname
 select @stringQ2 = @stringQ2 +  ' MOVE N'''+LogicalName+''' to N'''+@SQLData+ right(PhysicalName,charindex('\',reverse(PhysicalName))-1) +''''+','
 from #tmpT3
        
 select @stringQ2= @stringQ2 + 'NOUNLOAD,  STATS = 10 ';
  
 set @cnt = @cnt-1
  
 set @CreateSQL= 'echo '+@stringQ2 +'>' + @SQLPath +@dbname+ '.txt'
   
 set @CreateBat= 'echo ' + 'sqlcmd -E -i ' + @SQLPath +@dbname+ '.txt' +
     ' -o '+@LogPath+@dbname+ '.txt' +
     ' > ' +@batPath+@dbname+'.bat'
  
  
 exec   master..xp_cmdshell @CreateSQL,NO_OUTPUT
 exec   master..xp_cmdshell @CreateBat,NO_OUTPUT
  
end 
select @cnt= num from #tmpT2
  
while (1=1)
begin
  
 if (@cnt=0 or @cnt is null)
 break
  
 select @execBat = @batPath+dbname +'.bat'
 from #tmpT2
 where num = @cnt
   
 exec   master..xp_cmdshell @execBat,NO_OUTPUT
  
 set @cnt = @cnt-1
end 
 
 exec sp_configure 'xp_cmdshell' ,0
 reconfigure
 exec sp_configure 'show advanced option' ,0
 reconfigure