/*名称：还原数据库脚本功能：实现恢复到任意时间点满足条件：全备，差异备，日志备 在同一目录下*/ SET nocount ONDECLARE @BakSourcePath1 VARCHAR(500)DECLARE @BakSourcePath2 VARCHAR(500)DECLARE @time1 DATETIMEDECLARE @time2 DATETIMEDECLARE @dbname VARCHAR(200)DECLARE @pth_data VARCHAR(500)DECLARE @pth_log VARCHAR(500)----依据环境手工修改-----SET @dbname = '' ----还原后的数据库后缀名称，如为空('')则为备份文件中逻辑DBnameSET @BakSourcePath1 = 'D:\MSSQL\SQL2012\DBBak' ----备份文件（全备，差异，日志）存放位置SET @time1 = '2014-08-27 16:12' ----恢复到任意时间点设置，遵循时间格式（2013-11-11 22:33）SET @pth_data = 'D:\MSSQL\SQL2008R2\Data' ----还原后的数据文件路径SET @pth_log = 'D:\MSSQL\SQL2008R2\Log' ----还原后的日志文件路径-----------------------IF RIGHT(@pth_data, 1) <> '\' SET @pth_data = @pth_data + '\'IF RIGHT(@pth_log, 1) <> '\' SET @pth_log = @pth_log + '\'SELECT @time2 = CAST(@time1 AS DATETIME)--print @time2--SELECT REPLACE(CONVERT(VARCHAR(10),@time1-2,120),'-','/')SET @BakSourcePath2 = 'dir ' + @BakSourcePath1 + ' /OD /TC '--print @BakSourcePath2DECLARE @tb TABLE ( name VARCHAR(500) )INSERT INTO @tb EXEC master..xp_cmdshell @BakSourcePath2DELETE FROM @tbWHERE RIGHT(ISNULL(name, ''), 4) NOT IN ( '.bak', '.dif', '.trn' )--SELECT * INTO # FROM @TB --SELECT * FROM # --SELECT * FROM @tb DECLARE @tb2 TABLE ( filenames VARCHAR(500) , datetimes DATETIME )INSERT INTO @tb2 SELECT REVERSE(LEFT(REVERSE(name), CHARINDEX(' ', REVERSE(name)) - 1)) AS filenames , CAST(REVERSE(STUFF(STUFF(REVERSE(name), 1, CHARINDEX(' ', REVERSE(name)), ''), 1, CHARINDEX(' ', STUFF(REVERSE(name), 1, CHARINDEX(' ', REVERSE(name)), '')), '')) AS DATETIME) AS datetimes FROM @tb-------------------------full backup message IF OBJECT_ID('tempdb.dbo.#fullbackup') IS NOT NULL DROP TABLE tempdb.dbo.#fullbackupSELECT filenames AS fullname , datetimes AS createdateINTO #fullbackupFROM @tb2WHERE filenames LIKE '%.bak%' AND datetimes < @time2DECLARE @fullbackupname VARCHAR(500) --select cast('2013-10-11 06:07' as datetime)SELECT @fullbackupname = fullnameFROM ( SELECT TOP 1 fullname FROM #fullbackup ORDER BY createdate DESC ) a--print @fullbackupnameDECLARE @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.#diffbackupSELECT filenames AS diffname , datetimes AS createdateINTO #diffbackupFROM @tb2WHERE filenames LIKE '%.dif%' AND datetimes < @time2 AND datetimes > ( SELECT TOP 1 createdate AS createdate FROM #fullbackup ORDER BY createdate DESC )DECLARE @diffbackupname VARCHAR(500) DECLARE @LaterThenDiffcreatedate DATETIMESELECT @diffbackupname = diffbackupname , @LaterThenDiffcreatedate = createdateFROM ( SELECT TOP 1 diffname AS diffbackupname , createdate AS createdate FROM #diffbackup ORDER BY createdate DESC ) a--print @diffbackupnameDECLARE @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.#trnbackupSELECT filenames AS trnname , datetimes AS createdateINTO #trnbackupFROM @tb2WHERE filenames LIKE '%.trn%' AND datetimes >= ( CASE WHEN EXISTS ( SELECT TOP 1 1 FROM #diffbackup ) THEN @LaterThenDiffcreatedate ELSE ( SELECT TOP 1 createdate AS createdate FROM #fullbackup ORDER BY createdate DESC ) END ) AND datetimes <= ( CASE WHEN @time2 >= GETDATE() THEN ( SELECT MAX(datetimes) AS datetimes FROM @tb2 ) ELSE ( SELECT TOP 1 datetimes AS createdate FROM @tb2 WHERE datetimes > @time2 ORDER BY createdate ASC ) END ) 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, trnname AS trnbackupname , createdate FROM #trnbackup ORDER BY createdate ASC --select * from @trn_list--print @diffbackuppath --print @fullbackuppath -----------------------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 @tINSERT INTO @t EXEC ( 'restore filelistonly from disk=' + @fullbackuppath )DECLARE @dbname2 VARCHAR(200)DECLARE @version INTSELECT @version = CAST(SUBSTRING(@@VERSION, 21, 5) AS INT) IF @version < 2010 BEGIN DECLARE @t_header08 TABLE ( BackupName VARCHAR(500) , BackupDescription VARCHAR(500) , BackupType VARCHAR(500) , ExpirationDate VARCHAR(500) , Compressed VARCHAR(500) , Position VARCHAR(500) , DeviceType VARCHAR(500) , UserName VARCHAR(500) , ServerName VARCHAR(500) , DatabaseName VARCHAR(500) , DatabaseVersion VARCHAR(500) , DatabaseCreationDate VARCHAR(500) , BackupSize VARCHAR(500) , FirstLSN VARCHAR(500) , LastLSN VARCHAR(500) , CheckpointLSN VARCHAR(500) , DatabaseBackupLSN VARCHAR(500) , BackupStartDate VARCHAR(500) , BackupFinishDate VARCHAR(500) , SortOrder VARCHAR(500) , CodePage VARCHAR(500) , UnicodeLocaleId VARCHAR(500) , UnicodeComparisonStyle VARCHAR(500) , CompatibilityLevel VARCHAR(500) , SoftwareVendorId VARCHAR(500) , SoftwareVersionMajor VARCHAR(500) , SoftwareVersionMinor VARCHAR(500) , SoftwareVersionBuild VARCHAR(500) , MachineName VARCHAR(500) , Flags VARCHAR(500) , BindingID VARCHAR(500) , RecoveryForkID VARCHAR(500) , Collation VARCHAR(500) , FamilyGUID VARCHAR(500) , HasBulkLoggedData VARCHAR(500) , IsSnapshot VARCHAR(500) , IsReadOnly VARCHAR(500) , IsSingleUser VARCHAR(500) , HasBackupChecksums VARCHAR(500) , IsDamaged VARCHAR(500) , BeginsLogChain VARCHAR(500) , HasIncompleteMetaData VARCHAR(500) , IsForceOffline VARCHAR(500) , IsCopyOnly VARCHAR(500) , FirstRecoveryForkID VARCHAR(500) , ForkPointLSN VARCHAR(500) , RecoveryModel VARCHAR(500) , DifferentialBaseLSN VARCHAR(500) , DifferentialBaseGUID VARCHAR(500) , BackupTypeDescription VARCHAR(500) , BackupSetGUID VARCHAR(500) , CompressedBackupSize VARCHAR(500) ) DELETE FROM @t_header08 INSERT INTO @t_header08 EXEC ( 'restore headeronly from disk=' + @fullbackuppath ) SELECT @dbname2 = databasename + @dbname FROM @t_header08 ENDELSE BEGIN DECLARE @t_header12 TABLE ( BackupName VARCHAR(500) , BackupDescription VARCHAR(500) , BackupType VARCHAR(500) , ExpirationDate VARCHAR(500) , Compressed VARCHAR(500) , Position VARCHAR(500) , DeviceType VARCHAR(500) , UserName VARCHAR(500) , ServerName VARCHAR(500) , DatabaseName VARCHAR(500) , DatabaseVersion VARCHAR(500) , DatabaseCreationDate VARCHAR(500) , BackupSize VARCHAR(500) , FirstLSN VARCHAR(500) , LastLSN VARCHAR(500) , CheckpointLSN VARCHAR(500) , DatabaseBackupLSN VARCHAR(500) , BackupStartDate VARCHAR(500) , BackupFinishDate VARCHAR(500) , SortOrder VARCHAR(500) , CodePage VARCHAR(500) , UnicodeLocaleId VARCHAR(500) , UnicodeComparisonStyle VARCHAR(500) , CompatibilityLevel VARCHAR(500) , SoftwareVendorId VARCHAR(500) , SoftwareVersionMajor VARCHAR(500) , SoftwareVersionMinor VARCHAR(500) , SoftwareVersionBuild VARCHAR(500) , MachineName VARCHAR(500) , Flags VARCHAR(500) , BindingID VARCHAR(500) , RecoveryForkID VARCHAR(500) , Collation VARCHAR(500) , FamilyGUID VARCHAR(500) , HasBulkLoggedData VARCHAR(500) , IsSnapshot VARCHAR(500) , IsReadOnly VARCHAR(500) , IsSingleUser VARCHAR(500) , HasBackupChecksums VARCHAR(500) , IsDamaged VARCHAR(500) , BeginsLogChain VARCHAR(500) , HasIncompleteMetaData VARCHAR(500) , IsForceOffline VARCHAR(500) , IsCopyOnly VARCHAR(500) , FirstRecoveryForkID VARCHAR(500) , ForkPointLSN VARCHAR(500) , RecoveryModel VARCHAR(500) , DifferentialBaseLSN VARCHAR(500) , DifferentialBaseGUID VARCHAR(500) , BackupTypeDescription VARCHAR(500) , BackupSetGUID VARCHAR(500) , CompressedBackupSize VARCHAR(500) , Containment VARCHAR(500) ) DELETE FROM @t_header12 INSERT INTO @t_header12 EXEC ( 'restore headeronly from disk=' + @fullbackuppath ) SELECT @dbname2 = databasename + @dbname FROM @t_header12 END/*--------------------------------------------------Print Restore(full,diff,trn) SQL ----------------------------------------------------*/--(1) restore full backupSELECT *FROM @tDECLARE @i INTSET @i = 1DECLARE @LogicalName VARCHAR(256) , @PhysicalName VARCHAR(1000) , @Type VARCHAR(2) , @restorecmd VARCHAR(MAX) SET @restorecmd = 'restore database ' + @dbname2PRINT @restorecmdSET @restorecmd = 'from disk= ' + @fullbackuppath PRINT @restorecmdSET @restorecmd = 'with 'PRINT @restorecmdWHILE @i <= ( SELECT MAX(id) FROM @t ) BEGIN SELECT @LogicalName = LogicalName , @PhysicalName = PhysicalName , @Type = Type FROM @t WHERE id = @i IF RTRIM(UPPER(ISNULL(@Type, ''))) = 'D' SET @restorecmd = 'move ''' + @LogicalName + ''' to ''' + @pth_data + REVERSE(LEFT(REVERSE(@PhysicalName), CHARINDEX('\', REVERSE(@PhysicalName)) - 1)) + ''',' ELSE SET @restorecmd = 'move ''' + @LogicalName + ''' to ''' + @pth_log + REVERSE(LEFT(REVERSE(@PhysicalName), CHARINDEX('\', REVERSE(@PhysicalName)) - 1)) + ''',' PRINT @restorecmd SET @i = @i + 1 ENDPRINT 'norecovery,stats=5'PRINT 'go'IF EXISTS ( SELECT TOP 1 1 FROM #diffbackup ) BEGIN--(2) restore diff backup PRINT 'restore database ' + @dbname2 PRINT 'from disk=' + @diffbackuppath PRINT 'with norecovery,stats=5' PRINT ' ' PRINT 'go' PRINT ' ' END--(3) restore trn backup--select * from @trn_listDECLARE @id INTDECLARE @maxid INTDECLARE @restoreSQL VARCHAR(MAX)SELECT @id = 1 , @maxid = MAX(id)FROM @trn_listWHILE @id <= @maxid BEGIN SELECT @restoreSQL = 'restore log ' + @dbname2 + ' from disk =''' + @BakSourcePath1 + '\' + trnbackupname + CASE WHEN @id = @maxid THEN ''' with standby=''' + @BakSourcePath1 + '\' + @dbname2 + '.tuf'',stopat=''' + CASE WHEN @time2 >= GETDATE() THEN ( SELECT CONVERT(VARCHAR(100), MAX(datetimes), 120) AS datetimes FROM @tb2 ) ELSE CONVERT(VARCHAR(100), @time2, 120) END + '''' ELSE ''' with norecovery,stats=5' END FROM @trn_list WHERE id = @id PRINT @restoreSQL PRINT 'go' SET @id = @id + 1 END
