将每小时事务日志还原为每 10 分钟间隔一次

您应该能够编写一系列恢复日志（带备用）并指定STOPAT以 10 分钟为增量

--Full database restore with standby
USE master
GO
RESTORE DATABASE [StandbyTest]
FROM DISK = N'c:\StandbyTest\StandbyTest.bak'
WITH FILE = 1
    ,REPLACE
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
GO

--Pretend this was created at 10am
RESTORE LOG [StandbyTest]
FROM DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FILE = 1
    ,NOUNLOAD
    ,STATS = 10
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
    ,stopat = '2018-07-26 09:10:00' --10 minute interval
GO

--Copy data to another table

RESTORE LOG [StandbyTest]
FROM DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FILE = 1
    ,NOUNLOAD
    ,STATS = 10
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
    ,stopat = '2018-07-26 09:20:00'     --10 minute interval
GO

--Copy data to another table

RESTORE LOG [StandbyTest]
FROM DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FILE = 1
    ,NOUNLOAD
    ,STATS = 10
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
    ,stopat = '2018-07-26 09:30:00'     --10 minute interval
GO

--Copy data to another table

RESTORE LOG [StandbyTest]
FROM DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FILE = 1
    ,NOUNLOAD
    ,STATS = 10
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
    ,stopat = '2018-07-26 09:40:00'     --10 minute interval
GO

--Copy data to another table

RESTORE LOG [StandbyTest]
FROM DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FILE = 1
    ,NOUNLOAD
    ,STATS = 10
    ,STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'
    ,stopat = '2018-07-26 09:50:00'     --10 minute interval
GO

--

然后，继续处理在上午 11 点创建的下一个事务日志并重复还原命令，但更改STOPAT每个命令的时间。

您好，我已经创建了每 1 分钟恢复一次的脚本，然后将数据复制到另一个数据库中的另一个表。

create table #GARrestorefiles(
backupsetid int
,backupstartdate datetime
,backupfinishdate datetime
,databasename varchar(100)
,backupfile VARCHAR(100)
)
insert into #GARrestorefiles (backupsetid,backupstartdate,backupfinishdate,databasename,backupfile) 
SELECT bs.backup_set_id,bs.backup_start_date,(bs.backup_finish_date),bs.database_name,
bms.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE
s.dbid IN (5) 
AND bs.type = 'L'
--and backup_set_id = 1113
and backup_finish_date > '2018-07-27 08:45:00.000' 
--and backup_finish_date < '2018-07-25 13:00:00.000'
group by bs.database_name,bs.backup_set_id,bms.physical_device_name,bs.backup_start_date,bs.backup_finish_date
order by bs.backup_finish_date asc


DECLARE @DBfileToRestore VARCHAR(100),@Backupfinishdate datetime, @backupLocation varchar(500)
--, @mdfLocation varchar(500)
--, @ldfLocation varchar(500)

DECLARE restorecursor CURSOR FOR
SELECT backupfile,backupfinishdate FROM #GARrestorefiles

OPEN restorecursor

FETCH NEXT FROM restorecursor
INTO @DBfileToRestore,@Backupfinishdate

WHILE @@FETCH_STATUS = 0
BEGIN
    --SET @mdfLocation = 'F:\Files\Restore_Files\' + @DBfileToRestore + '.mdf'
    --SET @ldfLocation = 'F:\Files\Restore_Files\' + @DBfileToRestore + '.ldf'
--**********************************************************************************

--DECLARE @cmd nvarchar(4000)
DECLARE @srcbkpLocation nvarchar(4000)
DECLARE @destbkpLocation nvarchar(4000)
DECLARE @bkpFileName nvarchar(4000)
DECLARE @bkpFileName1 nvarchar(4000)
declare @dbname nvarchar(40)
DECLARE @date datetime;
DECLARE @Start_time datetime = DATEADD(minute,-5,@Backupfinishdate);
DECLARE @increase INT = 01;
DECLARE @STOPAT_time DATETIME = DATEADD(minute, @increase, @Start_time);
declare @backup_finish_date datetime
DECLARE @backupsetid int = 1878;

SET @backupLocation = @DBfileToRestore
SET @backup_finish_date = @Backupfinishdate
while(@STOPAT_time <= DATEADD(minute,01,@backup_finish_date))
    BEGIN
    PRINT @STOPAT_time
        RESTORE LOG AICS_QUERY_DEMO
        FROM DISK = @backupLocation
        WITH FILE = 1
        ,NOUNLOAD
        ,STATS = 10
        ,STANDBY = N'F:\SQL_Backup\AICS_Query\ROLLBACK_AICS_QUERY_DEMO.BAK'
        --,stopat = '2018-07-26 09:10:00' --10 minute interval
        ,stopat = @STOPAT_time
        SET @STOPAT_time = DATEADD(minute, @increase, @STOPAT_time);
    END
    INSERT INTO [RestoreDB].[dbo].[EmployeeDetail](Id,Name,Age,Location) 
    select Id,Name,Age,Location from [AICS_QUERY_DEMO].[dbo].[EmployeeDetail]
--Created a unique index on Id column with ignore duplicate ON to avoid duplicate entry.
--*******************************************************************************************
    FETCH NEXT FROM restorecursor
    INTO @DBfileToRestore,@Backupfinishdate
END
CLOSE restorecursor
DEALLOCATE restorecursor

我使用游标循环遍历不同的事务日志文件，然后使用 while 循环每 1 分钟停止一次，然后恢复一个表。