--批量还原完整备份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'