Recover the Marked Transaction for different users

This article describes the simulation process how to recover the DML/DDL Data while during the Deployment Process.

create database test

use test

create table employee

(

empno int primary key ,

empname varchar(10),

location varchar(10)

)

---Full backup available from Maintenance Plan

BACKUP DATABASE [test] TO  DISK = N'D:\Full_Backup\test.bak' WITH NOFORMAT,

NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Full backup taken for the required database as shown as below.

 First transaction screenshot as shown as below

Second  transaction screenshot as shown as below

Third  transaction screenshot as shown as below

Fourth  transaction screenshot as shown as below

---Now take the log backup

---we assume all the transaction executed by the users accidentally

---In order to recover we will be taking the log backup.

Restore full backup operation screenshot as shown as below

Restore the log backup in to newly database named called TestReplicate screenshot as shown as below

Recover the transaction before update  by using StopbeforeMark as shown as below

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPbeforemark= N'C3045' AFTER N'2013-04-19 17:15:30.717'

GO

Recover the transaction includes update  by using StopatMark

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPatmark= N'C3045' AFTER N'2013-04-19 17:15:30.717'

 GO

Recover the transaction Before Delete  by using StopbeforeMark

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPbeforemark= N'C3046' AFTER N'2013-04-19 17:20:39.943'

Recover the transaction includes Delete  by using StopatMark

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPatmark= N'C3046' AFTER N'2013-04-19 17:20:39.943'

GO

Recover the transaction before truncate  by using StopbeforeMark

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPbeforemark= N'C3046' AFTER N'2013-04-19 17:24:56.310'

  GO

Recover the transaction includes truncate  by using StopatMark

RESTORE LOG [TestReplicate]

 FROM  DISK = N'D:\Tran_Backup\test1.trn' WITH  FILE = 1,

  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TestReplicate.BAK',

   NOUNLOAD,  STATS = 10 ,STOPatmark= N'C3046' AFTER N'2013-04-19 17:24:56.310'

 GO