批量还原脚本

批量还原完整备份

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,stats=10,
    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

 

restore log v2

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[RestoreLogV1]    Script Date: 2020/1/7 16:39:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[RestoreLogV1] 
 @dbname varchar(500)
		,@filepath varchar(500)
as
declare @sqltxt varchar(max)
declare @filename varchar(1000)

SET NOCOUNT on

--set @dbname = 'maindb'
--set @filepath = 'D:\StandByLog\MainDB'

create table #tb(fname varchar(500))
insert into #tb
exec ('xp_cmdshell ''dir ' +@filepath +' /b /s /o:d''')

delete from #tb where fname is null

create table #lsntb
(
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed int
,Position smallint
,DeviceType tinyint
,UserName	nvarchar(128)
,ServerName	nvarchar(128)
,DatabaseName	nvarchar(128)
,DatabaseVersion	int
,DatabaseCreationDate	datetime
,BackupSize	numeric(20,0)
,FirstLSN	numeric(25,0)
,LastLSN	numeric(25,0)
,CheckpointLSN	numeric(25,0)
,DatabaseBackupLSN	numeric(25,0)
,BackupStartDate	datetime
,BackupFinishDate	datetime
,SortOrder	smallint
,CodePage	smallint
,UnicodeLocaleId	int
,UnicodeComparisonStyle	int
,CompatibilityLevel	tinyint
,SoftwareVendorId	int
,SoftwareVersionMajor	int
,SoftwareVersionMinor	int
,SoftwareVersionBuild	int
,MachineName	nvarchar(128)
,flags	int
,BindingID	uniqueidentifier
,RecoveryForkID	uniqueidentifier
,collation	nvarchar(128)
,FamilyGUID	uniqueidentifier
,HasBulkLoggedData	bit
,IsSnapshot	bit
,IsReadOnly	bit
,IsSingleUser	bit
,HasBackupChecksums	bit
,IsDamaged	bit
,BeginsLogChain	bit
,HasIncompleteMetaData	bit
,IsForceOffline	bit
,IsCopyOnly	bit
,FirstRecoveryForkID	uniqueidentifier
,ForkPointLSN	numeric(25,0)
,RecoveryModel	nvarchar(60)
,DifferentialBaseLSN	numeric(25,0)
,DifferentialBaseGUID	uniqueidentifier
,BackupTypeDescription	nvarchar(60)
,BackupSetGUID	uniqueidentifier
,CompressedBackupSize	bigint
,containment	tinyint
)

create table #lastres(dbname varchar(500), filepath varchar(500),firstlsn numeric(25,0), lastlsn numeric(25,0),backupstartdate datetime)

declare mycursor cursor for select fname from #tb where fname like '%.trn'
open mycursor
fetch next from mycursor into @filename 
while @@FETCH_STATUS = 0
begin 
	set @sqltxt = 'restore HEADERONLY  from disk = '''+@filename+''''
	truncate table #lsntb
	begin try
		insert into #lsntb
		exec (@sqltxt)

		insert into #lastres
		select @dbname,@filename,FirstLSN,LastLSN,BackupStartDate
		from #lsntb
	end try
	begin catch
		print '-------------'
		print ERROR_MESSAGE()
		print @sqltxt
		print '-------------'
	end catch
	fetch next from mycursor into @filename
end

close mycursor
deallocate mycursor



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


declare @startlsn numeric(25,0)
select @startlsn = redo_start_lsn from sys.master_files
where database_id = db_id(@dbname) and file_id = 1

declare restore_cur cursor for
select filepath from #lastres where lastlsn >= @startlsn order by backupstartdate asc
open restore_cur
fetch next from restore_cur into @filename
while @@FETCH_STATUS = 0
begin 

	set @sqltxt = 'restore log ['+@dbname+'] from disk = '''+@filename+''' with norecovery'
	print @sqltxt
	exec (@sqltxt)

	fetch next from restore_cur into @filename
	if @@FETCH_STATUS !=0
	begin 
			set @sqltxt =  'restore log ['+@dbname+'] from disk = '''+@filename+''' with standby = '''+@filepath+'\'+@dbname+'.dat'''
			print @sqltxt
			exec (@sqltxt)

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

	end
end


close restore_cur
deallocate restore_cur

drop table #lastres
drop table #tb
drop table #lsntb