Applied to MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions
MS SQL Server Database is widely used in the corporate world to manage important and confidential data. However, in the SQL server, data loss happens from time to time because of variable issues. For example, if you have executed the UPDATE or DELETE command without implementing the WHERE clause or wrong WHERE clause, you will encounter SQL table, row, or column loss. When the tragedy happens, you can recover the deleted records from the backup if you have created any before. If not, you have to resort to some further methods to make things done. On this page, we will show you two ways to recover deleted records in SQL Server successfully.
If you are not a technical user, you'd better use a professional MS SQL server recovery tool to help you recover deleted records instantly. Qiling MS SQL Recovery is a user-friendly program that enables you to recover deleted rows or damaged database and repair corrupt MDF file effectively.
Step 1.Download and run Qiling MS SQL Recovery.
Step 2.Click the two dots (Browse button) to choose the target MDF file or click "Search" and select the desired file to repair from the list found below. Then, click "Repair" to start the repairing process.
Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.
Step 4. Click the "Export" button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to "Export to database", you can create a new database or export to an existing database.
LSNs, short for Log Sequence Numbers,are unique identifiers that are givento every record in the SQL Server transaction logs. If you know the deletion time of records, you can use LSNs to recover deleted records in SQL server. The sooner you recover, the greater the chance of recovery.
Follow the step-by-stepinstructions below to use LSNs for deleted record recovery in SQL server2017,2016, 2015, 2014, 2012, 2008 and 2005.
Step 1. Execute the following query to know the number of rows contained in the table from which the records get deleted.
SELECT * FROM Table_name
Step 2. Now, use the query given below to create the backup of the transaction log of the SQL server.
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. Use the following query to get the information about the deleted records from the SQL Server table to recover data.
USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
Through this query, you will obtain the Transaction ID (000:000001f4) of deleted records, which will be used in the further process.
Step 4. Use the Transaction ID just got to find specific the time at which the records got deleted.
USE Databasename
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = '000:000001f4'
AND
[Operation] = 'LOP_BEGIN_XACT'
With the help of the above query, you will know the value of the current LSN.
Step 5. Now, recover the deleted data from the SQL Server Table by executing the following query.
Recover Deleted D 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. Implement LSN value to restore deleted rows with the following command.
USE Databasename
GO
Step 7. Lastly, check whether the deleted records are recovered to the SQL Table database or not.
USE Databasename_Copy GO Select * from Table_name