Recover data after using DELETE command in SQL Server

Restoring databases in SQL Server is a skill that every expert database administrator must possess. Data restoration in SQL Server provides an answer to how to recover data after using Delete command in SQL Server. It allows us to securely restore deleted records. Data restoration is a complex task involving execution of various complicated T-SQL queries. To recover deleted data from the SQL table the best solution is utilizing a database backup.

To recover the SQL Server database, you need to have a healthy backup. Therefore, to begin the data restoration, ensure your backup file is not corrupted. Also, to restore deleted database at a specific point in time, we recommend setting the database to the FULL RECOVERY MODE. Moreover, to restore the database at a specific time we will use Log Sequence Number (LSN). We will execute various Transact-SQL queries to restore data. Manually executing the T-SQL queries may be complicated for some users Therefore, be careful when writing queries to avoid permanent data loss.

Why recover data after using DELETE command in SQL Server?

The DELETE command is used by the admin or database user to remove records from the table. Then, why may the need arise to restore data? Below, we have given some scenarios leading to data restoration in SQL Server.

1. Accidental deletion: Often, the user deletes data accidentally. For example, the user was supposed to use the TRUNK statement. However, due to some miscommunication, they used the DELETE command.
2. Deleting records with wrong clause: We use the DELETE command with clause, defining the condition according to which the data will be deleted. However, using the wrong condition may lead to unintentional data deletion.
3. Needed old data for analysis: A sudden report generation requirement may come involving the old database table to run the analysis.
4. Testing new database environment: Your database admin might be testing a new environment to migrate the database. Before transfer of data, they may be testing or fine tuning the database by running all the important commands.

How to recover data after DELETE command in SQL Server?

We will make use of database backup to recover SQL Server database. Additionally, we will utilize the LSN to restore the database to a specific point in time. To explain the process, we will create and use a dummy database. Let us begin now:

1. Create a temporary database and table with the command below.

   GO
   
   CREATE DATABASE EmployeeTemp;
   GO
   USE EmployeeTemp;
   GO
   CREATE TABLE Employe (
   [Sr.No] INT IDENTITY,
   [Date] DATETIME DEFAULT GETDATE (),
   [City] CHAR (25) DEFAULT 'City1');
   

   
2. Add data into the table.

   USE EmployeeTemp;
   GO
   INSERT INTO Employe DEFAULT VALUES;
   GO 50
   

   
3. Create backup of the database. Right-click the database, select Task> Backup. In the newly opened window click OK to create the backup.
4. Delete some rows from the table with DELETE command.

   USE EmployeeTemp
   Go
   DELETE Employe
   WHERE [Sr.No] < 10
   GO
   Select * from Employe
   

   
5. Get information like Transaction Id, Current LSN etc. to restore deleted data. We need this information to pinpoint the exact transaction that deleted the data.

   USE EmployeeTemp
   GO
   SELECT
   [Current LSN], 
   [Transaction ID],
   Operation,
   Context,
   AllocUnitName
   FROM
   fn_dblog(NULL, NULL)
   WHERE
   Operation = 'LOP_DELETE_ROWS'
   

   
   Save the Transaction ID from the result. We will use it in the next steps.
6. Get Log Sequence Number(LSN).

   USE EmployeeTemp
   SELECT
   [Current LSN], 
   Operation,
   [Transaction ID],
   [Begin Time],
   [Transaction Name],
   [Transaction SID]
   FROM
   fn_dblog(NULL, NULL)
   WHERE
   [Transaction ID] = '0000:000003b2'
   AND
   [Operation] = 'LOP_BEGIN_XACT'

   
7. Finally, restore data with the T-SQL query below.

   --Restoring Full backup with norecovery.
   RESTORE DATABASE EmployeeTemp_COPY
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\EmployeeTemp.bak'
   WITH
   MOVE 'EmployeeTemp' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\EmployeeTemp.mdf',
   MOVE 'EmployeeTemp_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\EmployeeTemp.ldf',
   REPLACE, NORECOVERY;
   GO
    --Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
   RESTORE LOG
   EmployeeTemp_COPY
   FROM
   DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\EmployeeTemp_tlogbackup.trn'
   WITH
   STOPBEFOREMARK = 'lsn:0x00000027:0000001A:0001'
   

   

Now, verify if the data is restored with SELECT command.

Using the Log Sequencing approach allows you to precisely restore data in SQL Server. However, if you are missing table data due to some inconsistency in database. Then use DBCC CHECKDB to repair SQL Server table corruption issue. Alternatively, you can try a reliable database recovery tool.

How to recover data after DELETE command in SQL Server with a modern solution?

The manual method to restore database is very complicated. Any wrong step can throw error and lead to data restoration failure. Therefore, we recommend going for a simple and straightforward approach like a professional SQL recovery tool.

• Download Free
• Buy Now

Kernel for SQL Database Recovery is a sophisticated tool embedded with powerful algorithms. The tool deeply scans the database file and recovers deleted data from the SQL table. The tool accurately analyzes every database object and maintains complete data integrity.

The tool offers dual scanning mode - Standard Scan & Advanced Scan. When repairing a corrupt SQL database, you can choose the most appropriate one. After recovery, the tool also enables users to preview the recovered data and save only the required data objects or table.

The step-by-step process to repair MDF file of the database with the tool is given below:

Step 1 - Download, install, and launch SQL database recovery tool.

Step 2 - Click Browse to add the MDF file. Click Recover to scan & load it.

Note To recover deleted data from the SQL table, you must select the option - Include deleted records in recovery.

Step 3 - Open the table with damage.

Step 4 - Select the tables and click Save. Select Batch File in Saving Mode window and specify a saving location by clicking on Browse. Finally, click OK to start the recovery.

Step 5 - After the recovery process is completed, a message of success will be shown. Click OK to exit the process.

Conclusion

Perform a secure and fast data recovery with the Transact-SQL queries mentioned above. However, to restore data in SQL Server you need to possess the backup file. If you do not have any recent database backup, then we recommend utilizing a professional SQL database recovery tool – Kernel for SQL Database Recovery. This robust tool can safely and efficiently restore database in SQL Server.

• Download Free
• Buy Now

FAQ

Q: How to recover deleted records from the SQL table or database?

Ans: Yes, using the database backup, you can easily recover lost data.

Q: Why we need information like transaction id and LSN when recovering database?

Ans: With the help of transaction ID, we pinpoint the exact transaction that deleted the records. With the Transaction ID and the respected LSN, we can recover all records which got deleted in SQL Server.

Q: How can we restore the database to a specific time?

Ans: Using log LSN and FULL RECOVERY MODE, the database administrator can restore the database to a specific point in time.