Hi Today I will tell you how to recover the data in case data is deleted
Please follow step by step & execute next step when first step will be finished.
Step 1 : Create a new fresh database and a table inside this database
USE master
GO
CREATE DATABASE TestDb
GO
USE TestDb
GO
CREATE TABLE TestTable (
id INT identity(1, 1)
,NAME VARCHAR(50)
,Value INT
,ValueAt DATETIME DEFAULT(GetDate())
)
GO
Step 2 : Create a Full Backup of this database
BACKUP DATABASE TestDb TO DISK = 'D:\TestDb_Full.bak'
WITH init
,format
,stats = 10
GO
Step 3 : Follow below DML On table dbo.TestTable
USE TestDb
GO
INSERT INTO TestTable ( NAME,Value)
SELECT 'Vimal' ,50
UNION
SELECT 'Kamal' ,250
UNION
SELECT 'Raj' ,1200
UNION
SELECT 'Swastika',150
UNION
SELECT 'Sibin' ,125
UNION
SELECT 'Manas' ,560
UNION
SELECT 'Amit' ,3200
GO
SELECT * FROM TestTable
GO
DELETE FROM TestTable WHERE id > 5
GO
SELECT * FROM TestTable
GO
Step 4 : As data has been deleted. Now its time to recover that data.
Note : Here database is new & so as table. It is easy to recover. If you know the time of deleted data or approximate time you can recover data. But you will find it difficult if time range is too long or you forgot the time.
SELECT [Current LSN]
,[Transaction ID]
,[Operation]
,[Context]
,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
AND [AllocUnitName] = 'dbo.TestTable'
SELECT [Current LSN]
,[Operation]
,[Transaction ID]
,[Begin Time]
,[Transaction Name]
,[Transaction SID]
,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00000343'--<<Paste the TransactionID
AND [Operation] = 'LOP_BEGIN_XACT'
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1))
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1))
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1))
SELECT Cast(CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) AS VARCHAR(5)) + RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) AS NVARCHAR), 10) + RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5) AS 'MarkPoint'
GO
Step 5 : Take a log backup now. For this recovery model of database should be Full or Bulk-logged(Right click on Database>>Properties>>Options>>Recovery Model)
BACKUP log TestDb TO DISK = 'D:\TestDb_log.trn'
GO
Step 6 : Restore Full backup of database
RESTORE filelistonly
FROM DISK = 'D:\TestDb_Full.bak';
RESTORE DATABASE [TestDb_New]
FROM DISK = 'D:\TestDb_Full.bak'
WITH MOVE 'TestDb' TO 'C:\TestDb.mdf'
,MOVE 'TestDb_log' TO 'C:\TestDb_log.ldf'
,REPLACE
,NORECOVERY;
GO
Step 7 : Restore log backup of database like below
RESTORE LOG TestDb_New
FROM DISK = 'D:\TestDb_log.trn'
WITH STOPBEFOREMARK = 'lsn:33000000017600001'
GO
Step 8 : Check the table data
USE TestDb_New
GO
SELECT * FROM dbo.TestTable
--Export the deleted rows from this table to original table
Step 9 : Drop tables
USE master
GO
DROP DATABASE TestDb
DROP DATABASE TestDb_New
-->The End<--
Note : Step 4 time range
SELECT [Current LSN]
,[Operation]
,[Transaction ID]
,[Begin Time]
,[Transaction Name]
,[Transaction SID]
,[AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE
[Begin Time] BETWEEN '2015/07/17 15:30:00:000'
AND '2015/07/17 16:00:00:000'
