I am not sure whether this is restore data scenario is possible. Scenario:

I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in code),the data was getting deleted the same day whenever a new record which was not related that process comes into the table.So there is no specific time that this delete happens. We identified this after a week(yesterday),and i added a delete trigger for that table and stores the data into another table.

The issue is how can i restore past week's deleted data. I have transaction log backups which was taken every one hour. Without knowing the specific time the data got deleted,how can i do point in time recovery. Any restore strategy.?


It would be an arduous process, but you might be able to use RESTORE WITH STANDBY to a different instance. After each log is restored, you can read the data in the database. There is no guarantee the rows you are looking for haven't been added and deleted within the same transaction log backup.

RESTORE Statements

STANDBY =standby_file_name Specifies a standby file that allows the recovery effects to be undone. The STANDBY option is allowed for offline restore (including partial restore). The option is disallowed for online restore. Attempting to specify the STANDBY option for an online restore operation causes the restore operation to fail. STANDBY is also not allowed when a database upgrade is necessary.

The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.

On a side note (in reference to your statement about hourly transaction log backups), this situation is one of the reasons I like to Back Up Transaction Logs Every Minute. Yes, Really. for my critical databases.

Here is an example of using WITH STANDBY.

/* Create our test database.  You may need to change the file paths for your environment. */
( NAME = N'StandbyTest', FILENAME = N'C:\StandbyTest\StandbyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )


( NAME = N'StandbyTest_log', FILENAME = N'C:\StandbyTest\StandbyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
USE [StandbyTest]

/* Create some dummy data */
CREATE TABLE dbo.Customers (CustomerID INT, CustomerName VARCHAR(50))
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (1, 'Bill Gates')

INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (2, 'Steve Ballmer')


/* Do a full backup of the database */
BACKUP DATABASE [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.bak' 
NAME = N'StandbyTest-Full Database Backup', 

/* Add some more dummy data */
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (3, 'Donald Farmer')

/* Do a transaction log backup */
BACKUP LOG [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.trn' 
NAME = N'StandbyTest-Transaction Log  Backup', 

/* Pretend we're a developer */
DROP TABLE dbo.Customers

/* DISASTER!  We need to go back in time! */
USE master

 FROM  DISK = N'c:\StandbyTest\StandbyTest.bak' 
 WITH  FILE = 1,
 STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'


/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
/* Notice that we only got 2 customers, because we haven't restored the t-log yet. */

RESTORE LOG [StandbyTest] 

 FROM  DISK = N'C:\StandbyTest\StandbyTest.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10


/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
  • while researching about this..i saw somewhere that transaction log replaying..Right now i have implemented a delete trigger for that table.Same way can restore tlog and then replay it so that trigger gets executed and will insert the deleted records to a new table..i dunno the possiblity. Just wondering Jul 25 '18 at 17:52
  • Your comments are not clear to me. Whatever committed actions that are involved within each transaction log restore will be available for querying upon the restore of that transaction log (assuming either standby or with recovery). So, if you have a delete trigger writing deleted rows to a separate table and you delete 1 row (encompassed in the first tlog backup), that 1 row will be in the separate deleted table and you should be able to select it. Restore another transaction log and deleted rows affected in that tlog will be available for querying. Jul 25 '18 at 18:10
  • The other question is related to this question.I am trying to implement the answer you gave me in the other question.I will have a cursor to iterate through the different transaction log file and within the cursor i will have while loop to give different stopat times.I am not sure i will be successful as i am not that good at scripting. I saw you mentioned save table in the other post.I haven't thought of copy logic after each restore.I am not sure insert into another table will work on the db in STANDBY mode.?? Jul 26 '18 at 18:46
  • @user9516827 - the save table would have to be in another database, because the one you're restoring logs to will be read-only. Jul 26 '18 at 18:50
  • Yeah.okay correct.silly Jul 26 '18 at 18:56

From the sound of it, you don't have a single unknown time the data was deleted, you have multiple times that the data would have been deleted.

But good news! You have log backups that cover the time period in question, so it's not impossible. If I were faced with your requirements, I would do the following. It is entirely possible to script this process out, but the basic steps are:

  1. Decide on a timeframe that you want to restore to. Every minute, every 5 minutes, etc. You can go smaller, every second for example, but that will make the rest of this painful.

  2. Restore the full backup to a new database (or same database on another server).

  3. Copy contents of your table to a different table in another database, export, whatever.

  4. Restore the full backup, + first log with the stop at set to the time you want.

  5. GOTO Step 3 until you have all the data you want.

There may be third party tools that can do this for you as well, I know that some are capable of doing table level restores out of native backups. You can mix this strategy with the RESTORE WITH STANDBY that Scott Hodgin mentioned as well.

  • while researching about this..i saw somewhere that transaction log replaying..Right now i have implemented a delete trigger for that table.Same way can restore tlog and then replay it so that trigger gets executed and will insert the deleted records to a new table..i dunno the possiblity. Just wondering Jul 25 '18 at 17:52
  • I think you are misunderstanding a few concepts in your reply. Replaying transaction logs doesn't work the way you are thinking it does. While your trigger may help you in the future, it won't do anything about the data in the past. Jul 25 '18 at 18:15
  • Okay i understand now.I was wondering if it is possible.I couldn't find any option to set the restore time frame like 5 minutes in SSMS. Jul 25 '18 at 18:23
  • You are going to have to use TSQL, the GUI won't be of much help to you with this type of thing. docs.microsoft.com/en-us/sql/t-sql/statements/… Jul 25 '18 at 18:27
  • Feeling bad to ask you each and every detail.Never encountered any situation with restore to an old date and never did that process.I see that i have to use the below parameters with restore log ryt.\<point_in_time_WITH_options—RESTORE_LOG>::= | { STOPAT = { 'datetime'| @datetime_var } | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' } [ AFTER 'datetime'] | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' } [ AFTER 'datetime'] } Jul 25 '18 at 18:37

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

    --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))
    PRINT @STOPAT_time
        FROM DISK = @backupLocation
        WITH FILE = 1
        ,STATS = 10
        --,stopat = '2018-07-26 09:10:00' --10 minute interval
        ,stopat = @STOPAT_time
        SET @STOPAT_time = DATEADD(minute, @increase, @STOPAT_time);
    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
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.