-1

I have an hourly transaction log backup running for my production database. I have to implement the below scenario to check for a deleted data.

I have two tlogs.

  • tlog1.trn at 10 AM
  • tlog2.trn at 11 AM

I already have the recent full backup restored and differential till 09 AM restored.I want to restore tlog1.trn to every 10 minutes and copy data from a table say ,table1 to another table table2 in the same db or different db.

Using SQL Server 2016.

Is this a possible scenario?Do i have to use cursors.? I know that restore in STANDBY mode will let me read the data. Please help me how to restore to every 10 min restore point? I know that there is STOPAT option in restore command,but how can i restore to every 10 min interval.?Any syntax for that?

1
  • Actually some data got deleted during that time interval,so i am trying to retrieve that data,by restoring tlogs to every 5 minutes and then copy data to a table.Since we don't know that delete time,i have to go this way. Jul 26 '18 at 14:00
2

You should be able to code up a series of restore logs (with standby) and specify the STOPAT with 10 minute increments

--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

--

Then, proceed on to your next transaction log created at 11am and repeat the restore commands, but alter the STOPAT time for each one.

2
  • is there an option to give 10 minutes interval instead of specific time?because i have to do this for a large number of tlogs.I just mentioned 2 tlogs to make it easier. Jul 26 '18 at 14:22
  • 1
    If there are too many combinations of log backups and stopat points to type, a DBA would typically write a query that generates the script. Jul 26 '18 at 14:41
0

Hi i have created the script to restore every 1 minute and then copy the data to another table in another database.

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

I use a cursor to loop through different transaction log files and then a while loop to stopat every 1 minute and then restore a table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.