Someone had read a blog post by Paul Randal (blog|@PaulRandal) called Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN. In this blog post, Paul explains how to take the LSN for a transaction from a log dump and use it in the RESTORE command with the STOPBEFOREMARK option. In this case, the LSN is in a different format than is required for the RESTORE command. Paul Randal explains how to convert the format, but the explanation was still a little confusing for some people. This post is an attempt to clarify how to convert the LSN format and also provide an automated way to convert it.
If you missed any of the earlier posts in my DR series, you can check them out here:
If you get the LSN from a log dump with either fn_dblog() or DBCC LOG() or fn_dumpdblog(), the LSN format is in a string format that consists of 3 hexadecimal numbers delimited with colons. The restore command expects the LSN to be in a large integer format (we will actually be converting it to a varchar(26) data type). The process is to take each hexadecimal string and convert it and them put them back together again with the correct number of leading zeros.
We start with this format from the log file:
Paul Randal explained the format conversion really well in his post, but the part that was confusing some people was how to get the hexadecimal string to an integer string. With SQL Server 2008+, this is very simple to do by converting to a varbinary data type with a conversion style of 1 and then cast as integer. In order to convert it to varbinary, you first have to concatenate the string 0x and enough zeros to the hex string to bring the length of the hex string to 8 characters. Or to put into T-SQL terms:
CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) As int);
We do this with each of the three hex strings and then concatenate them with the appropriate leading zeros. for example, the resulting integer string would be 1127000000815200001 [1127 + (000000 + 8152) + (0000 + 1)] If we convert each hex string to variables of @LSN1, @LSN2, and @LSN3, the final concatenation would be:
CAST(@LSN1 as varchar(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) as varchar(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) as varchar(5));
First, we will create a database to perform our test in, make sure it is in simple recovery model, and then create a marked transaction:
Create Database TestLSN; Go -- Make sure it is simple recovery Alter Database TestLSN Set Recovery Simple; Go Use TestLSN; Go -- Create a marked transaction BEGIN TRAN Tran1 With MARK 'Tran 1'; Select * Into dbo.MyDatabases From sys.databases; Commit Go
We can look up the LSN for this transaction (ignoring the logmarkhistory table in the msdb database) by dumping the log file and looking for the transaction by name.
-- Find the LSN in the log Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name] From fn_dblog(null, null) Where [Transaction Name] = 'Tran1';
|Current LSN||Previous LSN||Operation||Context||Transaction Name|
But let’s assume that you don’t find about this right away and the transaction is no longer in the active part of the log. Does that mean that we’re out of luck? Here we will take a manual checkpoint and see that the transaction is no longer listed in the log:
-- Manual Checkpoint Checkpoint; -- Find the LSN in the log Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name] From fn_dblog(null, null) Where [Transaction Name] = 'Tran1';
Maybe we’re not out of luck though. There is a chance it could still be in the inactive portion of the log. We can use undocumented trace flag 2537 to view the inactive portion of the log. There is a bug with this trace flag (which I’ve been told will never be fixed because, after all, it’s undocumented) which prevents it from being used to read the log from a newly restored database.
-- No, really. Find the LSN in the log DBCC TraceOn (2537); Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name] From fn_dblog(null, null) Where [Transaction Name] = 'Tran1'; DBCC TraceOff (2537);
So we have our LSN (Current LSN is the column we want) and can convert it now. Just plug the “Current LSN” into the conversion script to get the properly formatted LSN.
-- Convert LSN from hexadecimal string to decimal string Declare @LSN varchar(22), @LSN1 varchar(11), @LSN2 varchar(10), @LSN3 varchar(5), @NewLSN varchar(26) -- LSN to be converted to decimal Set @LSN = '0000001e:00000038:0001'; -- Split LSN into segments at colon Set @LSN1 = LEFT(@LSN, 8); Set @LSN2 = SUBSTRING(@LSN, 10, 8); Set @LSN3 = RIGHT(@LSN, 4); -- Convert to binary style 1 -> int Set @LSN1 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) As int); Set @LSN2 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) As int); Set @LSN3 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) As int); -- Add padded 0's to 2nd and 3rd string Select CAST(@LSN1 as varchar(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) as varchar(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) as varchar(5));
This gives us the LSN in integer string format: 30000000005600001
Restoring data to or immediately before a specific transaction is a key tool in data recovery. If you can identify the LSN of the transaction that deleted data that shouldn’t have been deleted, you can use this process to restore the database to the last point immediately prior to the delete. More than likely, you will be restoring it to a different name and then using INSERT … SELECT queries to restore the data to the live database.
Download the demo script: Demo_ConvertLSNs.zip (1 KB)