https://dzone.com/articles/recover-deleted-data-from-sql-table-using-transact

restore deleted data .sql

Recover Deleted Data From SQL Server Table by Transaction Logs
There are some prerequisites to be fulfilled before we start the process to recover deleted data from SQL table. To easily recover deleted rows from a table in SQL Server database, it must have the BULK-LOGGED or FULL recovery model at the time when the deletion first occurred. Some quick actions are required so that the logs are still available to perform the recovery of data.

Follow the steps given below to recover deleted data from SQL Server 2005, 2008, 2012, 2014, and 2016 by the use of transaction logs.

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

1
SELECT * FROM Table_name
Step 2
Take the transaction log backup of the database using the query given below:

1
USE Databasename
2
GO
3
BACKUP LOG [Databasename]
4
TO DISK = N'D:\Databasename\RDDTrLog.trn'
5
WITH NOFORMAT, NOINIT,
6
NAME = N'Databasename-Transaction Log Backup',
7
SKIP, NOREWIND, NOUNLOAD, STATS = 10
8
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

1
USE Databasename
2
GO
3
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
4
FROM
5
fn_dblog(NULL, NULL)
6
WHERE Operation  = 'LOP_DELETE_ROWS'
From the query given above, we will obtain the 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:

1
USE Databasename
2
GO
3
SELECT
4
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
5
FROM
6
fn_dblog(NULL, NULL)
7
WHERE
8
[Transaction ID] = ‘000:000001f3'
9
AND
10
[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:

1
USE Databasename
2
GO
3
RESTORE DATABASE Databasename_COPY FROM
4
DISK = 'D:\Databasename\RDDFull.bak'
5
WITH
6
MOVE 'Databasename' TO 'D:\RecoverDB\Databasename.mdf',
7
MOVE 'Databasename_log' TO 'D:\RecoverDB\Databasename_log.ldf',
8
REPLACE, NORECOVERY;
9
GO
Step 6
Now apply transaction log to restore deleted rows by using LSN 00000020:000001d0:0001:

1
USE  Databasename
2
GO
3
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.

1
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.