批量还原V2

--批量还原完整备份V2

declare  @dbname varchar(500)      --还原的db名
        ,@filepath varchar(500)    --还原的路径
        ,@sqltxt varchar(max)      --sql 
        ,@filename varchar(1000)   --还原的文件名
        ,@fullday datetime      --完整备份到当前的时间差
        ,@srcpath varchar(500)     --备份服务器的地址 \ 网络路径

SET NOCOUNT on

--备份文件的存储路径

--备份文件拷贝到本机的路径
set @filepath = 'D:	mp'
--确定完整备份和当前日期的时间差,返回时间类型
set @fullday = getdate() - 4
set @srcpath = '\10.11.1.215d$standbylogofficial-server'

print @srcpath + ' ' + @filepath + ' '+ convert(char(10),@fullday,120)

/*从历史备份服务器拷贝数据到本机
默认拷贝当天的差异备份和当天的日志文件
*/

set @sqltxt = ' xp_cmdshell ''xcopy ' +@srcpath +'*.full ' + @filepath + '/q /s /h /d:'+convert(char(10),@fullday,101)+' /y'''
print @sqltxt
exec (@sqltxt)
set @sqltxt = ' xp_cmdshell ''xcopy ' +@srcpath +'*.diff ' + @filepath + '/q /s /h /d:'+convert(char(10),getdate(),101)+' /y'''
print @sqltxt
exec (@sqltxt)
set @sqltxt = ' xp_cmdshell ''xcopy ' +@srcpath +'*.trn '+ @filepath + '/q /s /h /d:'+convert(char(10),getdate(),101)+' /y'''
print @sqltxt
exec (@sqltxt)



/*2 生成临时的需要还原的文件列表*/

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

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,BackupTypeDescription varchar(60))

declare mycursor cursor for select fname from #tb 
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,BackupTypeDescription
        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


/*3 还原完整备份*/
declare restore_cur cursor for
select dbname,filepath from #lastres where BackupTypeDescription = 'Database'
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,stats=10'
    print @sqltxt
    exec (@sqltxt)
    fetch next from restore_cur into  @dbname,@filename
end
close restore_cur
deallocate restore_cur


/*4 还原每日差异备份*/


declare restore_curdiff cursor for
select b.dbname,b.filepath
from sys.sysdatabases a 
    inner join #lastres b on a.name = b.dbname
    inner join sys.master_files c on a.dbid = c.database_id and c.file_id = 1
where b.lastlsn > c.redo_start_lsn and b.BackupTypeDescription = 'Database Differential'
order by dbname,backupstartdate asc

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

/*5 还原每小时日志备份*/
declare @logdbname varchar(500)
declare restore_db cursor for select name from sys.sysdatabases where dbid > 4
open restore_db
fetch next from restore_db into @logdbname
while @@FETCH_STATUS = 0
    begin 

        declare restore_curtrn cursor for
        select b.dbname,b.filepath
        from sys.sysdatabases a 
            inner join #lastres b on a.name = b.dbname
            inner join sys.master_files c on a.dbid = c.database_id and c.file_id = 1
        where b.lastlsn > c.redo_start_lsn and b.BackupTypeDescription = 'Transaction Log' and b.dbname = @logdbname
        order by dbname,backupstartdate asc

        open restore_curtrn
        fetch next from restore_curtrn 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_curtrn into  @dbname,@filename
            if @@FETCH_STATUS !=0
            begin 
                    set @sqltxt =  'restore log ['+@dbname+'] from disk = '''+@filename+''' with standby = '''+@filepath+''+@dbname+'.dat'''
                    print @sqltxt
                    exec (@sqltxt)
            end
        end
        close restore_curtrn
        deallocate restore_curtrn

    fetch next from restore_db into @logdbname
end


close restore_db
deallocate restore_db

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



--sp_msforeachdb 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''ywmonitor'')  begin select max(insert_time),''?''as dbname from [?].dbo.dba_monitor_v2 end'