Ultimate Guide to Recover Deleted Records in SQL Server
For any database administrator, losing crucial data as a result of an error in deletion is a nightmare. One of the most widely used relational database management systems in use by companies around the world is SQL Server. It offers sophisticated data management and storage options, including backup and recovery procedures. Nevertheless, despite these safety measures, data loss can still happen for a number of reasons, including human mistakes, system failures, or hardware issues. We will here discuss in this article how we can recover deleted records in SQL server.
Get Familiar with Record Deletion From SQL Server
It is first important to recognise that a record in SQL Server cannot actually be physically erased from the disc when it is deleted. Instead, the system designates the space the record occupied as being open to new data. The deleted record can be recovered up until the point at which fresh data is written there.
There are different methods to recover deleted table in SQL server, depending on the situation and the available resources. So, let’s discuss the solutions available to restore or recover deleted rows in SQL server.
Efficient Ways to Recover Deleted Records in SQL Server – Tool Oriented
First, let’s discuss the quick and easy method to recover deleted rows in SQL server. A user can simply install the SQL Recovery Tool which is an ideal solution when required to recover deleted table in SQL server. The software’s ability to display deleted SQL database items in red colour as a preview is its strongest feature. Also, recoverable by the user are deleted SQL database objects including tables, views, functions, store procedures, and so forth.
The application supports exporting recovered table records into a SQL Server running on the same system or a different server network. Additionally, you have the option of saving the data in CSV format or as a SQL-compatible script. SQL Server versions 2019, 2017, 2016, 2015, and 2014 are supported by this tool.
1. Initially, launch the utility after downloading it and select Open to load the MDF file to recover deleted records in SQL server.
2. Choose the Advanced scanning mode and the Recover Deleted Objects box should then be checked after selecting the SQL Server MDF file version.
3. The erased data will be highlighted in red after the scanning process.
4. After selecting Export the recovered data to SQL Server Database under Export choices, fill in the necessary information.
5. Select the Destination Database menu Export Data from a New Database to an Existing Database.
6. Click the Export button to recover deleted records in SQL server, after choosing to Export the database either with just the schema or with the schema and data.
Recover Deleted Table in SQL Server Using Log Sequence Number (LSN)
Every record in the transaction logs of the SQL Server is given a special identification number called an LSN (Log Sequence Number). If the moment of their deletion is known, this enables the recover deleted records in SQL server. However, there are a few requirements that must be satisfied before the recovery process can begin, such as possessing a Full Recovery Model or Logged Recovery Model at the moment of data erasure.
Step 1: Initially, use the following query to determine how many rows were there in the table when the data was destroyed.
SELECT * FROM Table_name
Step 2: Next, take a second look at taking a log back using the following query:
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: To restore data, gather details about the deleted entries from the SQL Server table.
USE Databasename
GO
Select [Current LSN] LSN], Operation, Context, AllocUnitName, [Transaction ID],
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’
You may find the Transaction ID of deleted records with this query.
Step 4: Using the Transaction ID, run the query to determine the precise moment that the data were removed.
USE Databasename
GO
SELECT
Operation, [Current LSN], [Transaction Name], [Transaction SID] [Transaction ID], [Begin Time]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3′
AND
[Operation] = ‘LOP_BEGIN_XACT’
Step 5: Now, start the restore procedure to recover deleted records in SQL Server.
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, see if the SQL Table database has been able to recover any deleted records.
USE Databasename_Copy GO Select * from Table_name
Also Read: How to Repair Corrupted Master Database in SQL Server?
Conclusion
It is possible for users to recover deleted records in SQL server if the right techniques are used with the right guidance. Hence the best way to prevent SQL data loss is to have a robust backup and recovery strategy in place, including regular backups and testing the restore process.
For that, we have explained a few solutions on how to recover deleted data from SQL server database, which in return provide the expected results. A manual solution has some SQL queries to be executed. However, for no tech users, we have also discussed a most reliable automated solution which doesn’t requires any SQL query writing knowledge and helps to easily recover deleted table in SQL server.
