Thursday 1 December 2016

How to recover data after truncate the table using Log Files(SQL SEVER)

use master
create database Test
go


USE Test

GO

CREATE TABLE Student
(
    StudentID BIGINT IDENTITY PRIMARY KEY,
    StudentName VARCHAR(128),
    RollNo VARCHAR(10)
)
GO

BACKUP DATABASE Test  
TO DISK = 'D:\Backup\New folder\MyTestDB.BAK'  
GO


INSERT INTO Student(StudentName,RollNo)
VALUES
('Reza','101')
,('Hari','102')
,('Sunil','103')
,('Naveen','104')

GO

select * from Student


truncate table student

SELECT  
[Current LSN],      
Operation,  
[Transaction ID],  
[Begin Time],  
[Transaction Name]
FROM  
fn_dblog (NULL, NULL) where [Transaction Name] ='TRUNCATE TABLE'


--  00000020:00000194:0001  take LSN(Log Sequence Numbers) from above code.

Convert LSN number from HEX to Decimal number. like below code

SELECT CAST (CONVERT (VARBINARY,'0x'+'00000020', 1) AS INT) as FirstPart,     --32
CAST (CONVERT (VARBINARY,'0x'+'000001ed', 1) AS INT) as SecondPart,     --404
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as ThirdPart      --1
GO
results :
32,404,1

Add preceding zeros for 404 and 1.
Note : no need for 32
404 =0000000404 (7 zoroes) total shold be 10 didgits
1 =00001 (4 zeroes) total should be 5 digits.

--32000000040400001

Now do following steps one by one .

BACKUP LOG Test
TO DISK = 'D:\Backup\New folder\MyTestDB.TRN'  
GO


RESTORE DATABASE MyTestDB_Copy  
    FROM DISK = 'D:\Backup\New folder\MyTestDB.bak'  
WITH  
    MOVE 'Test' TO 'D:\Backup\New folder\MyTestDB.mdf',  
    MOVE 'Test_log' TO 'D:\Backup\New folder\MyTestDB_log.ldf',  
    REPLACE,NORECOVERY;      
    GO


RESTORE LOG MyTestDB_Copy  
FROM  
    DISK = N'D:\Backup\New folder\MyTestDB.TRN'  
WITH  
    STOPBEFOREMARK = 'LSN:32000000040400001'

use MyTestDB_Copy
select * from Student

No comments:

Post a Comment