how to

Restoring deleted records in Microsoft SQL Server can be a complex process, and it typically involves database backups and transaction logs. Here's a general outline of the steps you can follow: 1. Backup: Before attempting any recovery, ensure you have the latest backup of your database. This is crucial as it will serve as the base for the recovery process. 2. Transaction Log: If you have enabled the transaction log, SQL Server keeps track of all changes made to the data. You might be able to use this to restore deleted records. - Use the `RESTORE LOG` command to apply transaction log backups after the last full or differential backup. ```sql RESTORE LOG DatabaseName FROM DISK = 'PathToBackupFile' ``` 3. Using System Tables: If the deletion was recent and you haven't performed any other transactions, you might be able to find the deleted data in system tables like `sys.deleted_objects` (for objects) or `sys.traces` and `sys.fn_dblog` (for data changes). 4. Third-party Tools: There are also third-party tools available that can help recover deleted data directly, such as ApexSQL, SQL Safe, or Red Gate SQL Recover. These tools often provide a more user-friendly interface for the recovery process. 5. Undelete Feature: If you're using SQL Server Enterprise Edition, you might have the 'Change Data Capture' or 'Change Tracking' feature enabled. These can help you recover deleted data, but they need to be set up before the deletion occurred. Remember, data recovery is not guaranteed, and it's always best to have a robust backup strategy in place to prevent data loss. Always consult with a database administrator or IT professional if you're unsure about the process.

When you accidentally delete some records from SQL Server due to errors, wrong commands, corruption in SQL Server, or virus attacks, there's no need to worry. There are two effective ways to recover deleted records from SQL Server. You can utilize either the MS SQL Recovery tool or LSNs (Log Sequence Numbers) for this purpose.

Applies to: MS SQL Server 2017, 2016, 2014, 2012, 2008, and earlier versions

Microsoft SQL Server is widely used in the corporate world for managing and storing crucial and confidential data. However, data loss scenarios are quite common in SQL Server due to various issues. For instance, you might lose an SQL table, row, or column if you execute an UPDATE or DELETE command without a WHERE clause or with a wrong one. If tragedy strikes, you can restore deleted records from a backup if you created one beforehand. If not, you must resort to other methods to resolve the issue. On this page, we will introduce you to two successful methods to recover deleted records in SQL Server.

Methods to Recover Deleted Records in MS SQL Server Automatically

If you are not a technical user, it is advisable to use professional MS SQL Server recovery tools to help you instantly restore deleted records. The MS SQL Recovery Tool is a user-friendly program that allows you to effectively recover deleted rows or a corrupted database, as well as repair damaged MDF files.

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 choose the file to be repaired from the listed files below. Then click on 'Repair' to start the repairing process.

Step 3: Once the scanning is finished, a window will pop up to confirm that the analysis has been successfully completed. All the deleted tables and records will be listed in the left pane with their original table names.

< strong > Step 4. Click the 'Export' button in the bottom right corner of the screen. You can save the recovered database to a database and 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 to an existing one.

A method to manually recover deleted data in SQL Server using LSNs

LSN stands for Log Sequence Number, which is a unique identifier for each record in the SQL Server transaction log. If you know when the record was deleted, you can use the LSN to recover deleted records from a SQL Server. The sooner you attempt recovery, the more likely it is to be successful.

The following are step-by-step instructions for restoring deleted records using LSNs in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, and 2005.

Step 1: Run the following query to see how many rows are in the table that will have records removed:

SELECT * FROM 表名称;

Step 2. Now, run the following query to create a backup of your SQL Server transaction log.

Use the database name

Start

Backup log [Database Name]

TO DISK = 'N'D:\Databasename\RDDTrLog.trn'

without formatting, without initializing,

NAME = 'N'Database_Name - Transaction Log Backup',

Start

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

Use database name

Start

Select the [Current LSN], [Transaction ID], operation, context, and AllocUnitName

FROM can be translated as "from" or "coming from" in English.

fn_dblog(NULL, NULL) is a SQL function used to read the transaction log in SQL Server. In Chinese, it can be translated as "fn_dblog(NULL, NULL)" itself, since this is a specific function name that should be kept unchanged for technical accuracy. If you need to explain its meaning, you could say it is a "function that reads the database log with both parameters empty or unspecified."

where Operation equals 'LOP_DELETE_ROWS'

This query will return the Transaction ID (000:000001f4) of the deleted record, for use in subsequent procedures.

Step 4. Use the transaction ID you just found to figure out the exact time when the record was deleted.

Use database name

Start

Select

Current log sequence number (LSN), operation, transaction ID, start time, transaction name, and transaction session identifier (SID)

from

The Chinese translation of "fn_dblog(NULL, NULL)" is "fn_dblog(NULL, NULL)". This is a SQL function call that typically queries information from the database log. Its exact meaning may depend on the context in which it is used.

Where?

[Transaction ID] = '000:000001f4'

and

[Operation] = 'LOP_BEGIN_XACT'

This query will tell you the current LSN value.

Step 5. Now, to recover the deleted data from the SQL Server table, execute the following query.

"Recover deleted D USE database name"

GO can be translated as "start," "act," or, in certain contexts, "go."

RESTORE DATABASE Databasename_COPY FROM can be translated as "Restore a copy of the database Databasename from...". Please note, this appears to be a part of an SQL command used to restore a backup of a database. The exact translation might vary depending on the context.

This line of text indicates that there is a backup file named "RDDFull.bak" located in the folder "Databasename" on drive D:.

With

Move 'Databasename' to 'D:\RecoverDB\Databasename.mdf'.

Move 'Databasename_log' to 'D:\RecoverDB\Databasename_log.ldf'.

Replace without restoring.

Start

Step 6. To restore the deleted rows, run the following command with the LSN value:

Use database name

GO can be translated as "go" or "start."

Step 7. Finally, check that the deleted records have been restored to the SQL table database.

Select all columns from the Table_name table in the Databasename_Copy database

 Caution:: Recovering deleted records with LSN in SQL is a time-consuming process and requires technical knowledge to execute. Any mistake or incorrect query can lead to failed recovery. Therefore, if you are not a SQL Server expert, we highly recommend trying the automatic solution – use a SQL Recovery Tool software to get the job done safely and risk-free. After all, you can't afford to lose data in your SQL Server.