批量还原V2

declare  @dbname varchar(500)
        ,@filepath varchar(500)
declare @sqltxt varchar(max)
declare @filename varchar(1000)

SET NOCOUNT on

set @filepath = ‘D:\tmp\‘

set @sqltxt = ‘xp_cmdshell ‘‘forfiles -p ‘+@filepath+‘ /s /M *.full /D ‘+convert(char(10),getdate()-10,111)+‘ -C "cmd /c echo @path"‘‘‘


create table #tb(fname varchar(500))
insert into #tb
exec (@sqltxt)
delete from #tb where fname is null
update #tb set fname = replace(fname,‘"‘,‘‘)
from #tb

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 ‘%.full‘
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 DatabaseName,@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

declare restore_cur cursor for
select dbname,filepath from #lastres

open restore_cur
fetch next from restore_cur into @dbname,@filename
while @@FETCH_STATUS = 0
begin 
    set @sqltxt = ‘restore database [‘+@dbname+‘] from disk = ‘‘‘+@filename+‘‘‘ with norecovery‘
    print @sqltxt
    --exec (@sqltxt)
    fetch next from restore_cur into  @dbname,@filename
end
close restore_cur
deallocate restore_cur

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