to retrieve the table without backup

SELECT

[Current LSN],

Operation,

[Transaction Id],

[Transaction SID],

[Transaction Name],

[Begin Time],

[SPID],

Description

FROM fn_dblog (NULL, NULL)

WHERE [Transaction Name] = 'DROPOBJ'

Now run a transaction log backup on the database where the data was deleted if a transaction log backup has not ran since the data deletion. Next, we will restore this database to somewhere else or on same server with different name until the above LSN and then we will import the deleted data from newly restored database to your production database.

RESTORE DATABASE ReadingDBLog_COPY

FROM DISK = 'C:\ReadingDBLog_FULL_15JAN2014.bak'

WITH

MOVE 'ReadingDBlog' TO 'C:\ReadingDBLog.mdf',

MOVE 'ReadingDBlog_log' TO 'C:\ReadingDBLog_log.ldf',

REPLACE, NORECOVERY;

--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.

RESTORE LOG ReadingDBLog_COPY

FROM

DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn'

WITH

STOPBEFOREMARK = 'lsn:22000000042100001'

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
please refer this link:
https://www.easeus.com/sql-database-recovery/recover-deleted-sql-server-database-without-backup.html
Shiva Lenka8y ago
any one now this ????
Shiva Lenka8y ago
not having of
log.mdf file
and log.ldf file
we have any option
manoj singh8y ago
SELECT
[Current LSN],
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
[Begin Time],
[SPID],
Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

Now run a transaction log backup on the database where the data was deleted if a transaction log backup has not ran since the data deletion. Next, we will restore this database to somewhere else or on same server with different name until the above LSN and then we will import the deleted data from newly restored database to your production database.
RESTORE DATABASE ReadingDBLog_COPY
FROM DISK = 'C:\ReadingDBLog_FULL_15JAN2014.bak'
WITH
MOVE 'ReadingDBlog' TO 'C:\ReadingDBLog.mdf',
MOVE 'ReadingDBlog_log' TO 'C:\ReadingDBLog_log.ldf',
REPLACE, NORECOVERY;

GO
--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG ReadingDBLog_COPY
FROM
DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn'
WITH
STOPBEFOREMARK = 'lsn:22000000042100001'