批量还原数据库

--该脚本通过输入一个路径，来还原一个路径下所有的数据备份文件，

----脚本运行前作业-------------------------------------------------------------------------------------
use master 
go

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
Go
EXEC sys.sp_configure N'xp_cmdshell', N'1'  --
Go
RECONFIGURE WITH OVERRIDE
go
----------------------------------------------------------------------------------------------------------
--脚本正文
declare @strPath as varchar(50)
--需要还原的本地文件目录
set @strPath='E:\output'
declare @strFirstRunSql as Nvarchar(max)=N''
declare @strRunSql as Nvarchar(max)=N''
declare @DataBaseName as varchar(50)
declare @MaxOrder as char(6)
declare @oldfilName as varchar(100)

--获取文件夹下所有文件名称存入临时表
SET NOCOUNT ON
--存放目录下文件名
declare @FileList table(id int identity(1,1) primary key,fillname varchar(50),death int,isfile int)
--存放需要处理文件详细信息
declare @fileDesc table(id int identity(1,1) primary key,DataBaseName varchar(50),typename char(3),OrderNum char(6),oldstr varchar(100))

--存放bak详细信息
declare @bakFileDesc table(id int identity(1,1) primary key,LogicalName varchar(255), PhysicalName varchar(255),
						Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), 
						CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20),
						 ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20),
						 LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, 
						IsReadOnly VARCHAR(20), IsPresent VARCHAR(20),TDEThumbprint varchar(50) null)

insert into @FileList
EXEC Master.dbo.xp_DirTree @strPath,1,1

--字符串切割获取需要部分
insert into @fileDesc
select 	 left(fillname,charindex('_backup',fillname,1)-1) as DataBaseName
,right(fillname,3) as typename,left(right(fillname,10),6) as OrderNum,fillname as oldstr 
from @FileList 
where (right(fillname,3)='bak' or right(fillname,3)='dif') and  LEN(fillname)>29
order by DataBaseName,typename,OrderNum


declare TbCursor cursor 
	LOCAL 
	STATIC 
	FORWARD_ONLY 
	READ_ONLY 
for
select DataBaseName,OrderNum,oldstr
from @fileDesc as tb
where --databasename not in (select name from sys.databases with(nolock)) and 
	exists (
	select 1 from 
	(
	select  DataBaseName,max(OrderNum) as MaxOrder from @fileDesc 
	where typename ='bak' 
	group by DataBaseName
	)tb1	
	where tb.DataBaseName=tb1.DataBaseName and tb.OrderNum=tb1.MaxOrder
)

open TbCursor
FETCH NEXT FROM TbCursor INTO @DataBaseName,@MaxOrder,@oldfilName
WHILE @@fetch_status = 0      
BEGIN       
	--获得需要还原bak文件物理地址信息
	set @oldfilName= @strPath+'\'+@oldfilName
	insert   into   @bakFileDesc   
	exec('RESTORE FILELISTONLY FROM DISK = N'''+@oldfilName+'''') 

	--创建mdf文件保存文件夹
	set @strFirstRunSql=N''

	select @strFirstRunSql=@strFirstRunSql+N'exec master.dbo.xp_cmdshell ''mkdir'+N' '+
							 REVERSE(right(REVERSE(PhysicalName),len(PhysicalName)-CHARINDEX('\', REVERSE(PhysicalName), 1)))+N''''+CHAR(10)
	from @bakFileDesc
	exec (@strFirstRunSql)

	SET  @strRunSql=N''
	SELECT  @strRunSql=@strRunSql+strList
	from (
		--还原最新的完整备份
		select (N'RESTORE DATABASE '+ DataBaseName+' '+ CHAR(10)+
		N'FROM DISK='''+@strPath+'\'+ MAXoldstr+N''''+ CHAR(10)+
		N'WITH REPLACE,NORECOVERY ' + CHAR(10)+ CHAR(10)) as strList
		 from 
		(
			select DataBaseName,oldstr AS MAXoldstr  from @fileDesc 
			where typename =N'bak' and  DataBaseName=@DataBaseName and OrderNum=@MaxOrder
		) as tb
		union all 
		--还原完整备份之后的增量备份
		select strDesc
		from (
			select (N'RESTORE DATABASE '+ DataBaseName+N' '+ CHAR(10)+
			N'FROM DISK='''+@strPath+'\'+ oldstr+N''''+ CHAR(10)+
			N'WITH NORECOVERY ' + CHAR(10)+ CHAR(10)) as strDesc
			 from @fileDesc 
			where typename ='dif'  and DataBaseName=@DataBaseName and OrderNum>@MaxOrder
		) as tb
		union all 
		--还原数据库
		select N'RESTORE DATABASE '+@DataBaseName+N' WITH RECOVERY'+ CHAR(10)
	) as tb1

	exec (@strRunSql)
	WAITFOR DELAY '00:00:30'
FETCH NEXT FROM TbCursor INTO   @DataBaseName,@MaxOrder,@oldfilName
END  
CLOSE TbCursor      
DEALLOCATE TbCursor
--print @strRunSql

-------------脚本运行后

EXEC sys.sp_configure N'xp_cmdshell', N'0'  -- 为1则启用xp_cmdshell，为0则禁用xp_cmdshell
go
EXEC sys.sp_configure N'show advanced options', N'0' 
GO
RECONFIGURE WITH OVERRIDE
Go