Week 7 Extra Clue

SPOILER ALERT

Stop

reading

now

you

don’t

need

the

extra

clue.

Page down if you need the clue….

SPOILER ALERT

Stop

reading

now

you

don’t

need

the

extra

clue.

Page down if you need the clue….

This weeks extra clue is how to use LSN’s with a a tail of the log backup.

If you don’t know how to use the tail of the log backup, please take a look at the solution to Week 3 of the Database Corruption Challenge.

In Corruption Challenge 3 the entire tail of the log file was used, but what if you wanted to only pull back some of the transactions in the transaction log backup.

After you restore the full backup, and the log files leading up to the tail of the log backup as shown in Week 3, you would then modify the tail of the log restore from:

RESTORE LOG [CorruptionChallenge3]
   FROM DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Tail.trn'
   WITH NORECOVERY;

Changing it to include the STOPBEFOREMARK or STOPATMARK option.

RESTORE LOG [CorruptionChallenge3]
   FROM DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Tail.trn'
   WITH NORECOVERY, STOPBEFOREMARK='lsn:XXXXXXXX';

Where XXXXXXXX is the LSN of the place you want to stop at or stop before.

So where do I get the LSN?

There are several ways to get the LSN, but I like this one if the database is currently open.

First we are going to need a function that I created

CREATE FUNCTION [dbo].[dbhCrackLSN](@currentLSN AS VARCHAR(25))
RETURNS VARCHAR(25)
AS
BEGIN
	DECLARE @returnLSN as [numeric](25);

	;WITH lsnCte
	AS
	(
		SELECT CONVERT(INT, CONVERT(VARBINARY, '0x' + LEFT(@currentLSN, 8), 1)) AS Part1,
		CONVERT(INT, CONVERT(VARBINARY, '0x' + SUBSTRING(@currentLSN, 10, 8) , 1)) AS Part2,
		CONVERT(INT, CONVERT(VARBINARY, '0x' + RIGHT(@currentLSN, 4) , 1)) AS Part3
	)
	SELECT @returnLSN = CAST(Part1 as VARCHAR(10)) + RIGHT('0000000000' + CAST(Part2 as VARCHAR(10)), 10) + RIGHT('0000' + CAST(Part3 as VARCHAR(5)), 5)
	  FROM lsnCte;
	RETURN @returnLSN;
END

If you have been reading my blog for long, you will know how much I like using CTEs, so I had to use a CTE to clean up my code on this example.

Next after the function is created, you can query the fn_dblog function, but the LSNs aren’t formatted correctly.

SELECT [Current LSN] AS CurrentLSN,
	   [Begin Time] AS BeginTime,
	   [Transaction Name] AS TxnName
  FROM fn_dblog(null, null)
 ORDER BY [Begin Time] DESC;

So we use the dbhCrackLSN function that we created earlier to convert the LSNs from their hex format with colon seperators, to be the decimal usable format that the restore command expects.

SELECT [dbo].dbhCrackLSN([Current LSN]) AS UsableLSN,
       [Current LSN] AS CurrentLSN,
	   [Begin Time] AS BeginTime,
	   [Transaction Name] AS TxnName
  FROM fn_dblog(null, null)
 ORDER BY [Begin Time] DESC;

Once you have that output, you can the scroll back and find a point that you want to restore up to or restore before. You can then use the LSN in the tail of the log restore using the STOPBEFOREMARK or STOPATMARK options.

RESTORE LOG [CorruptionChallenge3]
   FROM DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Tail.trn'
   WITH NORECOVERY, STOPBEFOREMARK='lsn:XXXXXXXX';

Where XXXXXXXX is the LSN of the place you want to stop at or stop before.

Keep in mind that this is only a clue for Database Corruption Challenge #7.

Good Luck!

So where do I get the LSN?

Getting Help from Steve and the Team

Leave a Reply Cancel reply