数据库批量还原

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='D:\BackUp'  
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('_',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