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

  • I already have transaction logs taken.As far as i know logshipping will create new transaction logs and work with it.Please correct me if i am wrong. – user9516827 Jul 26 '18 at 13:53
  • It looks like you are trying to identify the STOPAT point for your RPO, correct? – scsimon Jul 26 '18 at 13:53
  • @scsimon I am not doing that.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. – user9516827 Jul 26 '18 at 14:00
  • Correct so you are trying to restore to the point before the data got deleted. – scsimon Jul 26 '18 at 14:00
  • @scsimon: Yes.Sorry i am not that familiar with RPO.I learned about it just now.Do you think restore to every fixed interval option is available.? All i see is STOPAT,where i have to give a fixed time. – user9516827 Jul 26 '18 at 14:03
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.

| improve this answer | |
  • 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. – user9516827 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. – David Browne - Microsoft Jul 26 '18 at 14:41
  • @user9516827 You can see the pattern that Scott has provided. You'll need to wrap this into a loop and process through each of the log backups. – mathewb Jul 26 '18 at 14:41
  • @mathewb,@David Browne .Unfortunately i am the accidental DBA here..and scripting this to automate seems very hard.I am trying to find a way to dynamically change the log file to do it for multiple files or loop through STOPAT times.I just started as a DBA and i couldn't find any post regarding data retrieval or similar stuff.But yeah i will try. – user9516827 Jul 26 '18 at 14:50
  • Question, how do you search in the database for deleted data? As far as I understand I'd have to restore the database and some logs and bring it to restored state. Search for data and if not there, repeat. If there are 12 logs to search, then it's 12 full and whatever log restores while searching for data between them. – Alen Jul 26 '18 at 17:46
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.

| improve this answer | |

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.