USE Master
GO
CREATE DATABASE [RecoverDeletedData]
ON PRIMARY
( NAME = N'RecoverDeletedData',
FILENAME = N'D:\RecoverDeletedData\RecoverDeletedData.mdf'
SIZE = 4096KB, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'RecoverDeletedData_log',
FILENAME = N'D:\RecoverDeletedData\RecoverDeletedData.ldf',
SIZE = 1024KB, FILEGROWTH = 10%)
GO
CREATE TABLE [Friends] (
[Sr.No] INT IDENTITY,
[Name] varchar (50),
[City] varchar (50));
USE RecoverDeletedData
GO
insert into friends values (1, 'IanRox', 'Delhi')
insert into friends values (2, 'Jim', 'New York')
insert into friends values (3, 'Catherine', 'Las Vegas')
insert into friends values (4, 'John', 'California')
insert into friends values (5, 'Katie', 'Mexico')
insert into friends values (6, 'Sabrina', 'Indiana')
insert into friends values (7, 'Alfred', 'Hamburg')
insert into friends values (8, 'Vaibhav', 'Bangalore')
insert into friends values (9, 'Vijeta', 'Mumbai')
insert into friends values (10, 'YashRox', 'Sultanpur')
USE RecoverDeletedData
GO
Select * from friends
USE RecoverDeletedData
GO
BACKUP DATABASE [RecoverDeletedData]
TO DISK = N'D:\RecoverDeletedData\RDDFull.bak'
WITH NOFORMAT, NOINIT, NAME = N'RecoverDeletedData-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE RecoverDeletedData
GO
DELETE friends
WHERE [Sr.No] >5
GO
select * from friends
USE RecoverDeletedData
GO
BACKUP LOG [RecoverDeletedData]
TO DISK = N'D:\RecoverDeletedData\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'RecoverDeletedData-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE RecoverDeletedData
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
USE RecoverDeletedData
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000002f1'
AND
[Operation] = 'LOP_BEGIN_XACT'
USE RecoverDeletedData
GO
RESTORE DATABASE RecoverDeletedData_COPY FROM
DISK = 'D:\RecoverDeletedData\RDDFull.bak'
WITH
MOVE 'RecoverDeletedData' TO 'D:\RecoverDB\RecoverDeletedData.mdf',
MOVE 'RecoverDeletedData_log' TO 'D:\RecoverDB\RecoverDeletedData_log.ldf',
REPLACE, NORECOVERY;
GO
USE RecoverDeletedData
GO
RESTORE LOG RecoverDeletedData_COPY FROM DISK = N'D:\RecoverDeletedData\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000025:000001d0:0001'
USE RecoverDeletedData_Copy
GO
Select * from friends
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
Commented:
I did not get one thing, when we are taking full backup of database before deletion of rows. Restore of that backup should directly give us all rows since we have taken backup before deletion of rows. Then why do we need transaction log backup?
Commented:
Thanks for sharing the article and, I really appreciate it. The deleted records are also repairable by Stellar Phoenix SQL Database Repair software. Here is the article: https://www.experts-exchan