For this post I want to show you how I recovered to a LSN where I did do this on a server where I wanted to go back to a time just before a delete occurred.

This is my setup.

ALTER DATABASE [AdventureWorks2014] SET RECOVERY FULL
GO

BACKUP DATABASE [AdventureWorks2014] TO DISK = 'C:\SQLSERVER\backups\AdventureWorks2014.bak'WITH COMPRESSION

BACKUP LOG [AdventureWorks2014] TO DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG1.bak'

-- DELETE occurred  (BAD USER)

DELETE FROM [Person].[Password]
WHERE BusinessEntityID = 2

BACKUP LOG [AdventureWorks2014] TO DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG2.bak'

Lets gain some insight into the transaction log.

SELECT * FROM
fn_dblog (NULL, NULL)

0000002e:00000348:0002 is The LSN where the transaction begins – LOP_BEGIN_XACT for transaction ID 0000:000015dd

This will be my recovery point. This LSN will be the STOPBEFOREMARK clause in my RESTORE code.

 RESTORE DATABASE [AdventureWorks2014DR]
    FROM DISK = 'C:\SQLSERVER\backups\AdventureWorks2014.BAK'
WITH
    MOVE N'AdventureWorks2014_Data' TO N'C:\SQLSERVER\AdventureWorks2014_Data2.mdf',
    MOVE N'AdventureWorks2014_Log' TO N'C:\SQLSERVER\AdventureWorks2014_log2.ldf',
   NORECOVERY
GO

RESTORE LOG [AdventureWorks2014DR]
FROM
    DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG1.bak'
WITH
NORECOVERY;

RESTORE LOG [AdventureWorks2014DR]
FROM
    DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG2.bak'
WITH
    STOPBEFOREMARK = 'lsn:0x0000002e:00000348:0002',
    NORECOVERY;
GO

RESTORE DATABASE [AdventureWorks2014DR] WITH RECOVERY;
GO

SELECT * FROM [Person].[Password]

Yes I have my delete back.

Let’s prove a point and go too far ahead. I will go past the commit; we wouldn’t expect it to be recovered right?

So let’s recover to LSN 0000002e:00000358:0001 (after the commit)

RESTORE DATABASE [AdventureWorks2014DR]
    FROM DISK = 'C:\SQLSERVER\backups\AdventureWorks2014.BAK'
WITH
    MOVE N'AdventureWorks2014_Data' TO N'C:\SQLSERVER\AdventureWorks2014_Data2.mdf',
    MOVE N'AdventureWorks2014_Log' TO N'C:\SQLSERVER\AdventureWorks2014_log2.ldf',
   NORECOVERY
GO

RESTORE LOG [AdventureWorks2014DR]
FROM
    DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG1.bak'
WITH
NORECOVERY;

RESTORE LOG [AdventureWorks2014DR]
FROM
    DISK = 'C:\SQLSERVER\backups\AdventureWorks2014LOG2.bak'
WITH
    STOPBEFOREMARK = 'lsn:0x0000002e:00000358:0001',
    NORECOVERY;
GO

RESTORE DATABASE [AdventureWorks2014DR] WITH RECOVERY;
GO

USE [AdventureWorks2014DR]
go
SELECT * FROM [Person].[Password]

Nope, it’s too far ahead.

Who did it?

Quite simply look for the Transaction SID for your Current LSN of interest.

 SELECT SUSER_SNAME(0x01050000000000051500000011C35F73E7CBDD7DA837D665F7620000) AS [WhoDidIt];