31 Days of Disaster Recovery

Welcome back to my series 31 Days of Disaster Recovery. Today is day 11, and today I want to talk about converting LSN formats. I had intended to write this blog post a long time ago, but I never seemed to get around to it. This started out as a question posted on Twitter’s #sqlhelp hash tag.

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:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors

Converting LSN Format

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: :: (e.g., 00000467:00001fd8:0001) and convert it to this format . Easy peezy, lemon squeezy.

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

Testing the Process

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

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

Summary

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)