How to Recover Deleted Table Records in SQL Server

If you deleted some rows and you do not have a backup or your backup does not work because it is damaged or corrupted, there is one solution to fix this problem.

Other reasons to lose some rows is a hacker attack, SQL injection, a virus, or a human error.

Or a person fired who deleted the data maliciously or other external reasons.

In this article, we will show how to recover deleted records in SQL Server database using a special software using the mdf file to recover the information.

Requirements

The following software will be used in this article in order to accomplish this demo:

• First, you will need SQL Server installed. You can use any SQL Server installed in Windows. Linux versions are not supported
• Secondly, make sure to have SQL Server Management Studio installed
• Stellar Repair for MS SQL software

Getting started

First, we will have a SQL Server table with data to do the tests.

SELECT * FROM [testDB].[dbo].[Currency]

Let’s delete a row to test how to recover it later. In this example we will delete the row with the Currency Code equal to AFA:

DELETE FROM [dbo].[Currency]
WHERE CurrencyCode ='AFA'
GO

Note that the truncate operations cannot be recovered. Only the delete operations can be recovered.

The solution

In order to restore the rows deleted, we will use the Stellar Repair for MS SQL. This software allows recovering deleted rows or damaged databases. You can download the demo version of the SQL Repair software from here:

• This is the main interface of the software:

• It will send you a message to stop your SQL Server database and create a copy in a different location. Press OK.

• If you do not know where your database is located, you can find it in SSMS in database properties, data file page:

• Also, in Stellar Repair for MS SQL, you have an option to search the database file:

• Make sure to stop the SQL Server Service:

• And create a copy of the database. Once that you create a copy, specify the mdf file in our software.

• Select the option Include Deleted Records.  This is the key to recover the data. Once press Repair.

• Software prompt to provide SQL Server version. As you can see, this software supports any SQL Server version:

• The software will show the Log Report and it will show the information including the database size, collation, path of the mdf file and list of tables and objects:

• The software will send a successful message. The software will show the tables, views, synonyms, Stored Procedures, Functions, Rules, Defaults, Data Types, Triggers, Sequences and Assemblies.

• Next, you have an option to save the database restored:

• It is possible to save the information in MDF, CSV, HTML or XLS (Excel). In this example, we will choose MSSQL:

• You can create a new database or work with the database online. You can restore in another SQL Server instance and specify the Authentication type which can be Windows Authentication or SQL Server Authentication. Also, you can specify the location of the files of the database:

Watch the complete working process of the SQL Recovery software:

Conclusion

In this article, we learned how to recover deleted records in SQL Server. These solutions can work in any SQL Server version in Windows. Stellar Repair for MS SQL works with the Main data file (.mdf) & secondary file (.ndf) and can recover the data in SQL Server or in other formats like comma separated value (CSV) files, HyperText Markup Language format (HTML) or Excel Spreadsheet (XLS). I found this software intuitive, simple to use and learn. It includes a help file, but it is not necessary because you can easily learn using the UI.

The software helps not only to recover deleted rows but also recovers the SQL database damaged by a virus, hacker attack or other external reasons.