Sql Data Recover

Step 1: Check the number of rows present in the Table from which the data has been accidentally deleted using the below-mentioned query

SELECT * FROM Table_name

Step 2: Take the transaction log backup of the database using the query given below

USE Databasename

GO

BACKUP LOG [Databasename]

TO DISK = N'D:\Databasename\RDDTrLog.trn'

WITH NOFORMAT, NOINIT,

NAME = N'Databasename-Transaction Log Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 3: In order to recover deleted data from SQL Server Table, we need to collect some information about the deleted rows. Run the query given below to achieve this purpose

USE Databasename

GO

Select [Current LSN] LSN], [Transaction ID], Operation, Context

, AllocUnitName FROM

fn_dblog(NULL, NULL)

WHERE Operation  = 'LOP_DELETE_ROWS'

From the query given above, we will obtain Transaction ID(Let’s say 000:000001f3) of the deleted rows. Now the time when these rows were deleted is to be determined using this ID.

Step 4: In this step, we will find the specific time at which rows were deleted using the transaction ID 000:000001f3. This is done by executing the query given as follows

USE Databasename

GO

SELECT

[Current LSN],  Operation, [Transaction ID], [Begin Time]

, [Transaction Name] , [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f3'

AND

[Operation] = 'LOP_BEGIN_XACT'

On executing this query we will get the value of current Log Sequence Number(LSN) (Let’s say 00000020:000001d0:0001)

Step 5: Now we will start the restore process to recover deleted data from SQL Server table rows that was lost. This is done using the below query

USE Databasename

GO

RESTORE DATABASE Databasename_COPY FROM

DISK = 'D:\Databasename\RDDFull.bak'

WITH

MOVE 'Databasename' TO 'D:\RecoverDB\Databasename.mdf',

MOVE 'Databasename_log' TO 'D:\RecoverDB\Databasename_log.ldf',

REPLACE, NORECOVERY;

GO

Step 6: Now apply transaction log to restore deleted rows by using LSN “00000020:000001d0:0001”

USE  Databasename

GO

RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' 

WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001'  

Note: Since LSN values are in Hexadecimal form and for restoring 

tables using this LSN, 

we need to convert it into decimal form. For this purpose

, we add 0x just before the LSN as shown above.

Step 7: The process to recover deleted records from SQL table will get completed successfully. Now check whether the deleted records are back in the database named ‘Databasename_Copy’.

USE  Databasename_Copy

GO

Select * from Table_name

Disadvantages of Transaction Log Approach

§  Highly time-consuming method to recover deleted data from SQL Server table as it involves several lengthy queries to be executed

§  Extremely complex to implement for users, not possessing adequate technical knowledge

§  Greater chances of losing data due to errors while application and execution of queries

Conclusion

Although the Log Sequence Number method can restore deleted records from SQL Tables, it is not a recommended option for users due to its complexity and tediousness. Instead, it is advised to use an automated solution to recover deleted data from SQL Server table.