USA: +1 888 900 4529     UK: +44 800 088 5522
support@systoolsgroup.com

banner

How to Get Back or Recover Deleted Records in SQL Server 2008?

Ashwani Tiwari | Modified: 2019-10-01T12:14:12+00:00 | Recovery, SQL Server, Tips|

“One of my colleagues accidentally ran DELETE command on a Table having most important data with a wrong WHERE clause. We use Microsoft SQL Server 2014. We don’t have any backup of the data. How to recover deleted records in SQL Server 2014?”

The above-discussed query is a real-time issue raised by a user. There are times when users accidentally perform UPDATE operation or DELETE operation in SQL server 2005, 2008, 2012, 2014, 2016, 2017 database without adding WHERE clause or with wrong WHERE condition.

This is one of the common scenarios that occur during the management and maintenance of records where they face disastrous database situations. So in order to go back to the normal workflow, it is necessary to retrieve or get back deleted record in SQL Server 2016, 2014, 2012, 2008, 2005 database.

In some cases, many people intentionally deleted the records from the SQL database. To analyze who deleted data from a Table in SQL Server  and wants to recover deleted data from log file in SQL Server then, take the help of the SQL Log Analyzer Software. An efficient utility to read data of a .ldf file and recover in the designated format.

How to Recover Deleted Records in SQL Server Manually?

While considering manual solutions to restore deleted rows from the SQL Server database, the availability of Backup file of your data is very important. It is sure that, if you have maintained a proper backup of your SQL Server Database time to time, it will help you to get back your deleted rows from SQL Server database.

Now there are prerequisites on your SQL Backup also. Because a differential backup does not help you always. To perform some available manual solutions, you need the full backup of your SQL Server database. Follow the steps if you are having the backup.

  1. Restore the backup of SQL database
  2. Keep both Databases individually
  3. Find removed data from backup
  4. Perform UPDATE operation on altered records

Are your Systems is in Full Recovery Mode or Logged Recovery Mode?

This is a direct question. The chance of recovering your deleted rows or records depends upon these recovery modes. In the case of retrieving deleted records in SQL Server manually using LSN (Log Sequence Number), the prerequisite is to have Full Recovery or Bulk-Logged Recovery Mode enabled in the Server.

How to Restore Deleted Record in SQL Server Using LSN?

The LSN are unique in nature. Each record in a table will be having LSN in a unique SQL Server LDF file. Therefore, a deleted record in a SQL table can be easily recovered if the time of their deletion is known correctly.

To recover deleted records in SQL Server using LSN, there are few prerequisites that are to be fulfilled as discussed above. For a fine recovery of deleted rows (records) from the table in SQL Server database, you need to have Full Recovery Model or Logged Recovery Model at the time when the data deletion took place.

Steps to Retrieve Deleted Records from Table in SQL Server

To recover deleted rows in SQL Server 2017, 2016, 2015, 2014, 2012, 2008 and 2005, follow the below steps without any mistakes.

Step 1:- Check the number of rows existing in the table from which records or rows were deleted.

SELECT * FROM Table_name

Step 2:- Now take a log backup.

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:- Get the Transaction ID of deleted Records. It will help you to get information about deleted rows.

USE DatabaseName
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’

Step 4:- Now find specific time at which the rows (records) were deleted. The Transaction ID will help you to find this information. This steps will also help you to find the ongoing LSN.

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’

Step 5:- Now start the process to recover deleted data from records in SQL Server Table.

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:- Finally, check the table whether deleted rows are recovered or not.

USE DatabaseName_Copy GO Select * from Table_name

All the above-discussed solutions can only help when you have the most updated backup of your database. In many scenarios, the above mentioned steps gets fail. Because

  • Deficiency of good backup
  • Entry of new data in the rows
  • Case of altered database rows are not defined

Alternative Solution to Recover Deleted Records in SQL Server

The best alternative solution is available to retrieve rows from SQL Server 2017/ 2016/ 2014/ 2012/ 2008/ 2005 . The alternative solution that we discuss here can resolve all kind issues that arises in the SQL Server. It can fix SQL errors and repair SQL data files like MDF and NDF. The SysTools SQL Database Recovery is a commercial application which allows recovering Triggers, Rules, Functions, Tables. In addition, it also restore Stored Procedures in SQL Server easily without any data loss. The important part is, the application provides many advanced options while repairing corrupted SQL Server database files. Download the free demo edition to understand the proper function of the application.

  1. After downloading the Software, launch the application, and open the MDF file.
  2. Select Advance Scan option to recover deleted records in SQL Server.
  3. After scanning process completes, select Export as SQL Server Database.
  4. You need to provide the Database authentication details in the upcoming section.
  5. Check ‘Create New Database’ option and provide a Database Name.
  6. The software provides the option to export data ‘With only Schema’ or ‘With Schema & data’.
  7. Now Export the Database ‘With schema & data’ so as to recover data in the deleted rows in the SQL Server.
  8. Now save the report and open the newly created database which contains Recovered records.

To see proper step by step working of the software, watch the video given below. It clearly shows how to get back deleted records in SQL Server 2008, 2012, 2014, 2016 without any data loss.

Conclusion:

It is sure that you would have got the solution for your query. Because the solutions discussed in the blog will definitely help you to recover deleted records in SQL Server. The manual, as well as automated application discussed in this blog, will help you in resolving the issue. If you couldn’t retrieve deleted rows in SQL Server 2008, 2012, 2014, 2016, 2017 version using LSN method, try the third party software to restore records in database files. It will help you, resolving the issue in very simple steps.

Comments are closed.

Exclusive Offers & Deals, Grab it Now!

Systool Offer