How to Recover Deleted Rows in SQL Server [2024]

Applies to MS SQL Server 2017, 2016, 2014, 2012, 2008, and other versions.

Restoring deleted rows in SQL Server can be a complex process, especially if you don't have a recent backup. However, there are a few methods you can try: 1. **Transaction Log**: If you have enabled the transaction log and the deletion was part of a transaction that hasn't been committed yet, you can use `ROLLBACK TRANSACTION` to undo the deletion. ```sql BEGIN TRANSACTION -- Your DELETE statement here ROLLBACK TRANSACTION ``` 2. **Temporal Tables**: If you're using SQL Server 2016 or later and have set up temporal tables, you can retrieve deleted data from the system versioned history table. ```sql SELECT * FROM

How can I recover deleted rows or records in SQL Server?

Have you ever run into a situation where you needed to delete rows, tables, pages, or columns in your SQL Server while managing your business or serving your customers? It's a common issue that most administrators have probably encountered or may need to deal with in the future.

Oftentimes, when a database or a database component is deleted, an MS SQL Server admin tries to restore it from a backup. But what do you do when you delete a database or a table in SQL without having a backup? This article gathers two solutions to help you recover single or multiple rows, tables, pages, or columns using an automated SQL recovery tool or a manual method with SLN files.

Next, we'll explore how to recover lost data and get your operations back on track.

Using SQL Recovery Software to Restore Deleted Rows in SQL Server [Simple]

One effective way to recover lost SQL rows is by using an automated SQL recovery tool. Indeed, a powerful SQL recovery software can save you time and effort in restoring your SQL Server data.

While searching online, make sure to opt for 100% secure software offered by an experienced company. A tool with more than a decade of experience in data recovery recently launched professional SQL Recovery Software to help you fix SQL Server issues.

With it, you can easily recover deleted records, including restoring deleted data in SQL, fixing corrupted databases, MDF file recovery, and more. In just a few simple clicks, you can get your lost SQL Server rows back:

Step 1. Download and run the SQL Recovery tool.

Step 2: Click on the two dots (Browse button) to select the target MDF file, or click on “Search,” and then choose the file to be repaired from the listed files below. Click on “Repair” to start the repairing process.

Step 3: Once the scanning process is completed, a window will pop up to confirm successful analysis. All recovered tables and records will be displayed in the left pane under their original table names.

< strong > Step 4. Click the “Export” button in the bottom right corner of the screen. Here, you can save the recovered database to a database and/or an SQL script as needed. Then, you need to enter the server/instance name and connect to the server. If you choose “Export to Database,” you can create a new database or export data to an existing one.

After saving the deleted records, including your lost row, to an MDF file, you can import it or save it back to the original location in your SQL Server database.
Then, restart and run SQL Server again.

Manually Recovering SQL Server Rows with LSNs [Complicated]

A more complex approach is to use LSNs (Log Sequence Numbers) to check and recover deleted rows.

Caution: Proceed with caution while recovering deleted rows from an SQL database following these steps.

Step 1: Check the number of existing rows in the SQL table, excluding the deleted rows.

Select all columns from the Table_name table.

Step 2. Run the following query to restore the logs:

Use the database name
GO
Backup log [Database Name]
To Disk = N'D:\Database Name\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'Database Name - Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO This script is written in SQL and is used to back up the transaction log of a specified database. Here's a breakdown of each part: 1. `USE Database Name`: Switches to the database named "Database Name". 2. `GO`: Serves as a delimiter for executing SQL statements. 3. `BACKUP LOG [Database Name]`: Begins the log backup operation, where `[Database Name]` is your actual database name. 4. `TO DISK = N'D:\Database Name\RDDTrLog.trn'`: Specifies the path and file name where the backup will be saved. 5. `WITH NOFORMAT, NOINIT`: Does not format new media and does not initialize existing backup sets. 6. `NAME = N'Database Name - Transaction Log Backup'`: Assigns a descriptive name to the backup set. 7. `SKIP, NOREWIND, NOUNLOAD`: Skips existing media header checks, does not rewind the backup device, and does not unload the backup device. 8. `STATS = 10`: Displays progress information every 10% complete during the backup process. Replace "Database Name" with the actual name of your database and ensure the backup path is correct.

Step 3: Use the following query to retrieve information about the deleted records from the SQL Server table:

Use the database name
GO
SELECT [Current LSN], [Transaction ID], Operation, Context, Allocation Unit Name
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'

That way, you get the transaction ID of the deleted record.

< strong>Step 4.</strong> Use the transaction ID to find the exact time when the record was deleted:

Use the database name
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. Recover deleted data (including rows) from a SQL Server table:

RESTORE DATABASE [Databasename_COPY] FROM DISK = N'D:\Databasename\RDDFull.bak' WITH MOVE N'Databasename' TO N'D:\RecoverDB\Databasename.mdf', MOVE N'Databasename_log' TO N'D:\RecoverDB\Databasename_log.ldf', REPLACE, NORECOVERY; GO

Step 6. Check that the deleted records (including rows) have been restored to the SQL table database:

Select all columns from the Table_name table in the Databasename_Copy database

Reminder: Backup MS SQL Server databases regularly

Knowing how to recover lost records or rows from an SQL Server table database is not enough.

It's also important to back up SQL databases on a regular basis. An automatic SQL Server backup tool is an excellent choice for creating a full backup of your large SQL Server database. If you're looking for a smart way to protect your SQL database, then Todo Backup Advanced Server is a must-try option.