Restore a Database to a Marked Point

SQL Server Log backup can restore to a marked transaction. When you mark Update/Insert or any other transactions, you can recover data up to a marked transaction.

Very Simple example to restore a Database to a marked point.

---- Create LOGTEST Database

CREATE DATABASE LOGTEST

USE LOGTEST

-----Perform Full backup

backup database LOGTEST

to disk=N'D:\LOGTEST\LOGTEST.BAK'

-----Create MARK table

USE LOGTEST

BEGIN TRANSACTION createtable WITH MARK 'create table'

CREATE TABLE [dbo].[MarkTest](

       Id  int null,

       Name char(50) null

       )

commit Transaction createtable

-----Insert test data 1

BEGIN TRANSACTION InsertName WITH MARK 'INSERT DATA 1'

Insert into MarkTest (Id,Name)

values(1,'Test Data 1')

commit Transaction InsertName

-----Insert test data 2

BEGIN TRANSACTION InsertName1 WITH MARK 'INSERT DATA 2'

Insert into MarkTest (Id,Name)

values(2,'Test Data 2')

commit Transaction InsertName1

select * from LOGTEST.dbo.MarkTest

-----After a few hours you noticed that all inserts after 'test data 1' are wrong and you ----------don't have records.

-----Now you want to restore the database up to insert of 'test data 1' 

-----Perform Log backup

backup log LOGTEST

To Disk =N'D:\LOGTEST\LOGTEST_trn.BAK'

-----Restore Full backup

USE master

restore database LOGTEST

From Disk=N'D:\LOGTEST\LOGTEST.BAK'

WITH REPLACE,NORECOVERY

-----Restore Log backup up to Marked point

Restore Log LOGTEST

from disk =N'D:\LOGTEST\LOGTEST_trn.BAK'

with recovery,Stopbeforemark='InsertName1'

SQL has two options for Recover to a Mark

WITH STOPMARK = Recover transactions with the specific marked point

WITH STOPBEFOREMARK= Recover transactions before the marked Point