还原数据库脚本

发布时间:2014-10-23 23:27:34
来源:分享查询网

/* 名称:还原数据库脚本 功能:实现恢复到任意时间点 满足条件:全备,差异备,日志备 在同一目录下 */ SET nocount ON DECLARE @BakSourcePath1 VARCHAR(500) DECLARE @BakSourcePath2 VARCHAR(500) DECLARE @time1 DATETIME DECLARE @time2 DATETIME DECLARE @time3 DATETIME DECLARE @dbname VARCHAR(200) DECLARE @pth_data VARCHAR(500) DECLARE @pth_log VARCHAR(500) ----依据环境手工修改----- SET @dbname = '_test201311201038' ----还原后的数据库名称,如为空('')则为备份文件中逻辑DBname SET @BakSourcePath1 = 'E:\bak' ----备份文件(全备,差异,日志)存放位置 SET @time1 = '2013-10-17 18:10' ----恢复到任意时间点设置,遵循时间格式(2013-11-11 22:33) SET @pth_data = 'E:\Data' ----还原后的数据文件路径 SET @pth_log = 'E:\Data' ----还原后的日志文件路径 ----------------------- SELECT @time2 = CAST(@time1 AS DATETIME),@time3 = dateadd(hh,1,CAST(@time1 AS DATETIME)) --print @time2 --print @time3 SELECT @BakSourcePath2 = 'forfiles /p ' + @BakSourcePath1 + ' /c "cmd /c echo @file-@fdate @ftime"' --print @BakSourcePath2 DECLARE @tb TABLE ( name VARCHAR(500) ) INSERT INTO @tb EXEC master..xp_cmdshell @BakSourcePath2 -------------------------full backup message IF OBJECT_ID('tempdb.dbo.#fullbackup') IS NOT NULL DROP TABLE tempdb.dbo.#fullbackup SELECT name AS fullname , CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) AS createdate INTO #fullbackup FROM @tb WHERE name IS NOT NULL AND name LIKE '%.bak%' AND CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) < @time2 DECLARE @fullbackupname VARCHAR(500) --select cast('2013-10-11 06:07' as datetime) SELECT @fullbackupname = fullbackupname FROM ( SELECT TOP 1 SUBSTRING(fullname, CHARINDEX('"', fullname) + 1, CHARINDEX('-', fullname) - 3) AS fullbackupname FROM #fullbackup ORDER BY createdate DESC ) a --print @fullbackupname DECLARE @fullbackuppath VARCHAR(500) SELECT @fullbackuppath = '''' + @BakSourcePath1 + '\' + @fullbackupname + '''' --print @fullbackuppath -------------------------diff backup message IF OBJECT_ID('tempdb.dbo.#diffbackup') IS NOT NULL DROP TABLE tempdb.dbo.#diffbackup SELECT name AS diffname , CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) AS createdate INTO #diffbackup FROM @tb WHERE name IS NOT NULL AND name LIKE '%.dif%' AND CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) < @time2 DECLARE @diffbackupname VARCHAR(500) DECLARE @LaterThenDiffcreatedate DATETIME SELECT @diffbackupname = diffbackupname , @LaterThenDiffcreatedate = createdate FROM ( SELECT TOP 1 SUBSTRING(diffname, CHARINDEX('"', diffname) + 1, CHARINDEX('-', diffname) - 3) AS diffbackupname , CAST(SUBSTRING(diffname, CHARINDEX('-', diffname) + 1, LEN(diffname)) AS DATETIME) AS createdate FROM #diffbackup ORDER BY createdate DESC ) a --print @diffbackupname DECLARE @diffbackuppath VARCHAR(500) SELECT @diffbackuppath = '''' + @BakSourcePath1 + '\' + @diffbackupname + '''' --print @diffbackuppath -----------------------trn backup message IF OBJECT_ID('tempdb.dbo.#trnbackup') IS NOT NULL DROP TABLE tempdb.dbo.#trnbackup SELECT name AS trnname , CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) AS createdate INTO #trnbackup FROM @tb WHERE name IS NOT NULL AND name LIKE '%.trn%' AND CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) > @LaterThenDiffcreatedate AND CAST(SUBSTRING(name, CHARINDEX('-', name) + 1, LEN(name)) AS DATETIME) < @time3 DECLARE @trn_list TABLE ( id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , trnbackupname VARCHAR(300) , createdate DATETIME ) INSERT INTO @trn_list SELECT --row_number() over (order by createdate asc) as id, SUBSTRING(trnname, CHARINDEX('"', trnname) + 1, CHARINDEX('-', trnname) - 3) AS trnbackupname , createdate FROM #trnbackup ORDER BY createdate ASC -----------------------Get Original DatabaseName from restore filelistonly DECLARE @t TABLE ( id INT IDENTITY(1, 1) PRIMARY KEY , LogicalName VARCHAR(256) , PhysicalName VARCHAR(1000) , Type VARCHAR(2) , FileGroupName VARCHAR(256) , Size BIGINT , MaxSize BIGINT , FileId INT , reateLSN VARCHAR(100) , DropLSN VARCHAR(100) , UniqueId VARCHAR(100) , ReadOnlyLSN VARCHAR(100) , ReadWriteLSN VARCHAR(100) , BackupSizeInBytes BIGINT , SourceBlockSize BIGINT , FileGroupId INT , LogGroupGUID VARCHAR(300) , DifferentialBaseLSN VARCHAR(100) , DifferentialBaseGUID VARCHAR(300) , IsReadOnly VARCHAR(2) , IsPresent VARCHAR(2) , TDEThumbprint VARCHAR(100) ) DELETE FROM @t INSERT INTO @t EXEC ( 'restore filelistonly from disk=' + @fullbackuppath ) DECLARE @LogicalName VARCHAR(200) DECLARE @Logical_LogName VARCHAR(200) SELECT @LogicalName = logicalname FROM @t WHERE type = 'D' SELECT @Logical_LogName = logicalname FROM @t WHERE type = 'L' --print @LogicalName --print @Logical_LogName DECLARE @dbname2 VARCHAR(200) SELECT @dbname2 = @LogicalName + @dbname --print @dbname2 /*-------------------------------------------------- Print Restore(full,diff,trn) SQL ----------------------------------------------------*/ --(1) restore full backup PRINT 'use master' PRINT 'go' PRINT 'restore database ' + @dbname2 PRINT 'from disk=' + @fullbackuppath PRINT 'with' PRINT 'move ''' + @LogicalName + ''' to ''' + @pth_data + '\' + @dbname2 + '.mdf'',' PRINT 'move ''' + @Logical_LogName + ''' to ''' + @pth_log + '\' + @dbname2 + '.ldf'',' PRINT 'norecovery,stats=5' PRINT ' ' PRINT 'go' PRINT ' ' --(2) restore diff backup PRINT 'restore database ' + @dbname2 PRINT 'from disk=' + @diffbackuppath PRINT 'with norecovery,stats=5' PRINT ' ' PRINT 'go' PRINT ' ' --(3) restore trn backup --select * from @trn_list DECLARE @id INT DECLARE @maxid INT DECLARE @restoreSQL VARCHAR(MAX) SELECT @id = 1 , @maxid = MAX(id) FROM @trn_list WHILE @id <= @maxid BEGIN SELECT @restoreSQL = 'restore log ' + @dbname2 + ' from disk =''' + @BakSourcePath1 + '\' + trnbackupname + CASE WHEN @id = @maxid THEN ''' with standby=''' + @BakSourcePath1 + '\' + @dbname2 + '.tuf'',stopat='''+convert(varchar(100),@time2)+'''' ELSE ''' with norecovery,stats=5,stopat='''+convert(varchar(100),@time2)+'''' END FROM @trn_list WHERE id = @id PRINT @restoreSQL PRINT 'go' SET @id = @id + 1 END  

返回顶部
查看电脑版