批量还原完整备份 批量还原增量备份 批量还原日志

批量还原完整备份 :

declare @filepath nvarchar(50)
,@dbname varchar(500)
,@dbpath nvarchar(600)
,@dbnamefile nvarchar(500)
,@sqltxt nvarchar(max)
,@datafile nvarchar(500)
,@logfile nvarchar(500)

set @filepath = 'd:\temp'

create table #dbname (dbnamefile varchar(500))

create table #dbtmp(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileId bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
tdethumbprint varchar(50)
)

insert into #dbname
exec ('xp_cmdshell ''dir /b '+@filepath+'''')

--select * from #dbname

delete from #dbname
where dbnamefile not like '%.full' or dbnamefile is null

declare mycur cursor for select dbnamefile from #dbname
open mycur
fetch next from mycur into @dbnamefile
while @@FETCH_STATUS = 0
begin
set @dbpath = @filepath + '\' + @dbnamefile
set @dbname = substring(@dbnamefile,1,CHARINDEX('_33_',@dbnamefile)-1)
print @dbpath
print @dbname

truncate table #dbtmp
set @sqltxt = 'restore filelistonly from disk = '''+@dbpath+''''
insert into #dbtmp
exec(@sqltxt)

select @datafile = LogicalName from #dbtmp where Type = 'd'
select @logfile = LogicalName from #dbtmp where Type = 'l'

--set @sqltxt='Alter database ['+@dbname+'] set RESTRICTED_USER with rollback immediate'
--exec (@sqltxt)

set @sqltxt = 'restore database ['+@dbname+'] from disk = '''+@dbpath+'''
with replace,norecovery,
move '''+@datafile+''' to ''d:\database\'+@datafile+'.mdf'',
move '''+@logfile+''' to ''d:\database\'+@logfile+'.ldf''
'
print @sqltxt
exec (@sqltxt)

fetch next from mycur into @dbnamefile
end
close mycur
deallocate mycur

drop table #dbname
drop table #dbtmp

 

批量还原增量备份：

 

declare @filepath nvarchar(50)
        ,@dbname varchar(500)
        ,@dbpath nvarchar(600)
        ,@dbnamefile nvarchar(500)
        ,@sqltxt nvarchar(max)
        ,@datafile nvarchar(500)
        ,@logfile nvarchar(500)

set @filepath = 'd:\temp'

create table #dbname (dbnamefile varchar(500))

create table #dbtmp(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileId bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
tdethumbprint varchar(50)
)

insert into #dbname
exec ('xp_cmdshell ''dir /b '+@filepath+'''')

--select * from #dbname

delete from #dbname
where dbnamefile not like '%.diff' or dbnamefile is null

declare mycur cursor for select dbnamefile from #dbname
open mycur
fetch next from mycur into @dbnamefile
while @@FETCH_STATUS = 0
begin 
    set @dbpath = @filepath + '\' + @dbnamefile
    set @dbname = substring(@dbnamefile,1,CHARINDEX('_33_',@dbnamefile)-1)
    print @dbpath
    print @dbname

    --truncate table #dbtmp
    --set @sqltxt = 'restore filelistonly from disk = '''+@dbpath+''''
    --insert into #dbtmp
    --exec(@sqltxt)

    --select @datafile  = LogicalName from #dbtmp where Type = 'd'
    --select @logfile  = LogicalName from #dbtmp where Type = 'l'

    set @sqltxt = 'restore database ['+@dbname+'] from disk = '''+@dbpath+''' with norecovery
    '
    print @sqltxt
    -- exec (@sqltxt)

fetch next from mycur into @dbnamefile
end
close mycur
deallocate mycur


drop table #dbname
drop table #dbtmp

批量还原日志：

create table #logname (logfile varchar(500))
create table #logname_l(logfile nvarchar(500),no bigint)


declare @filepath varchar(500),
        @logfile varchar(500)
        ,@logpath varchar(500)
        ,@dbname  varchar(500)
        ,@sqltxt varchar(max)

set @filepath = 'D:\temp'

insert into #logname
exec ('xp_cmdshell ''dir  /b '+@filepath+' *.trn''')

insert into #logname_l(logfile,no)
select logfile,replace(replace(SUBSTRING(logfile,charindex('_No',logfile),22),'_no',''),'.trn','') as no from #logname where logfile like '%.trn' 



declare mycur cursor for select logfile from #logname_l order by no asc
open mycur
fetch next from mycur into @logfile
while @@FETCH_STATUS = 0

begin 


    set @dbname = SUBSTRING(@logfile, 1, CHARINDEX('_no',@logfile)-1)
    set @logpath = @filepath + '\' + @logfile
    
    set @sqltxt = 'restore log ['+@dbname+'] from disk = '''+@logpath+''' with norecovery'
    print @sqltxt

    exec(@sqltxt)
    fetch next from mycur into @logfile
end

close mycur
deallocate mycur


drop table #logname
drop table #logname_l

转自牛B的DBA的链接：https://www.cnblogs.com/luck001221/p/9922227.html