数据库恢复到特定时间点的详细步骤
/*
名称：还原数据库脚本
功能：实现恢复到任意时间点
满足条件：全备，差异备，日志备 在同一目录下
*/
SET nocount ON
DECLARE @BakSourcePath1 VARCHAR(500)
DECLARE @BakSourcePath2 VARCHAR(500)
DECLARE @time1 DATETIME
DECLARE @time2 DATETIME
DECLARE @dbname VARCHAR(200)
DECLARE @pth_data VARCHAR(500)
DECLARE @pth_log VARCHAR(500)
----依据环境手工修改-----
SET @dbname = ''
----还原后的数据库后缀名称，如为空('')则为备份文件中逻辑DBname
SET @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 @BakSourcePath2
DECLARE @tb TABLE ( name VARCHAR(500) )
INSERT INTO @tb
EXEC master..xp_cmdshell @BakSourcePath2
DELETE FROM @tb
WHERE 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.#fullbackup
SELECT filenames AS fullname ,
datetimes AS createdate
INTO #fullbackup
FROM @tb2
WHERE filenames LIKE '%.bak%'
AND datetimes < @time2
DECLARE @fullbackupname VARCHAR(500)
--select cast('2013-10-11 06:07' as datetime)
SELECT @fullbackupname = fullname
FROM ( SELECT TOP 1
fullname
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 filenames AS diffname ,
datetimes AS createdate
INTO #diffbackup
FROM @tb2
WHERE 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 DATETIME
SELECT @diffbackupname = diffbackupname ,
@LaterThenDiffcreatedate = createdate
FROM ( SELECT TOP 1
diffname AS diffbackupname ,
createdate 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 filenames AS trnname ,
datetimes AS createdate
INTO #trnbackup
FROM @tb2
WHERE 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 @t
INSERT INTO @t
EXEC ( 'restore filelistonly from disk=' + @fullbackuppath
)
DECLARE @dbname2 VARCHAR(200)
DECLARE @version INT
SELECT @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
END
ELSE
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 backup
SELECT *
FROM @t
DECLARE @i INT
SET @i = 1
DECLARE @LogicalName VARCHAR(256) ,
@PhysicalName VARCHAR(1000) ,
@Type VARCHAR(2) ,
@restorecmd VARCHAR(MAX)
SET @restorecmd = 'restore database ' + @dbname2
PRINT @restorecmd
SET @restorecmd = 'from disk= ' + @fullbackuppath
PRINT @restorecmd
SET @restorecmd = 'with '
PRINT @restorecmd
WHILE @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
END
PRINT '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_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='''
+ 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
扫一扫
02-24
459
459
01-18
1390
1390
12-20
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
