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