SQL Server 2016 – Restore Deleted Data from Table Occurring Daily

logsrestoresql-server-2016transaction-log

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

Best Answer

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. */
CREATE DATABASE [StandbyTest] ON  PRIMARY 
( NAME = N'StandbyTest', FILENAME = N'C:\StandbyTest\StandbyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON 

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

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

 VALUES (1, 'Bill Gates')

INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (2, 'Steve Ballmer')

GO

/* Do a full backup of the database */
BACKUP DATABASE [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.bak' 
WITH FORMAT, INIT,  
NAME = N'StandbyTest-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* 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' 
WITH FORMAT, INIT,  
NAME = N'StandbyTest-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

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

/* DISASTER!  We need to go back in time! */
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

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

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
Related Question