The script is applicable to various versions of MS SQL Server, including 2017, 2016, 2014, 2012, 2008, and older versions.
SQL Server Database is widely used in the corporate world to manage important and confidential data. However, data loss can occur due to various issues, such as executing UPDATE or DELETE commands without a WHERE clause or with a wrong WHERE clause, resulting in SQL table, row, or column loss. In such cases, recovering deleted records can be challenging, but having a backup can help. If no backup exists, alternative methods must be employed to recover the lost data. This page will outline two ways to successfully recover deleted records in SQL Server.
If you're not tech-savvy, it's best to use a professional MS SQL server recovery tool to help you recover deleted records quickly. Qiling MS SQL Recovery is a user-friendly program that allows you to recover deleted rows or damaged databases and repair corrupt MDF files effectively.
Step 1.Download and run Qiling MS SQL Recovery.
Step 2.To repair a damaged MDF file, click the two dots (Browse button) to select the target file or click "Search" to find the file in a list, and then click "Repair" to initiate the repair process.
Step 3. When the recovery process is complete, a confirmation window will appear, listing all the recovered tables and records, which were previously deleted, in the left pane under their original table names.
Step 4. To export the recovered database, click the "Export" button in the bottom right corner of the screen, then select the desired export option, such as exporting to a database or SQL scripts. Next, enter the Server/Instance Name and connect to the server, and choose whether to create a new database or export to an existing one.
LSNs (Log Sequence Numbers) are unique identifiers assigned to every record in SQL Server's transaction logs. Knowing the deletion time of records allows you to use LSNs to recover deleted records, and the sooner you recover, the higher the chance of success.
Follow the step-by-stepinstructions below to use LSNs for deleted record recovery in SQL server2017,2016, 2015, 2014, 2012, 2008 and 2005.
Step 1. `SELECT COUNT(*) FROM table_name WHERE condition;`
SELECT * FROM Table_name
Step 2. To create a backup of the transaction log of the SQL server, use the following query: `BACKUP LOG [YourDatabaseName] TO DISK = 'C:\Backup\TransactionLogBackup.bak' WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;` Replace [YourDatabaseName] with the name of your actual database.
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. Use the following query to get the information about the deleted records from the SQL Server table to recover data.
USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
You will get the Transaction ID (000:000001f4) of deleted records, which will be used in the further process.
Step 4. You can use the Transaction ID to track the specific time at which the records were deleted. This is useful for auditing and understanding the sequence of events surrounding the deletion.
USE Databasename
GO
SELECT
The current Local Security Number (LSN) is [Current LSN]. The operation being performed is [Operation].
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = '000:000001f4'
AND
[Operation] = 'LOP_BEGIN_XACT'
The query will return the current LSN value, which is the most recent transaction log sequence number. This value is used to track changes made to the database and is essential for maintaining consistency and integrity.
Step 5. Now, recover the deleted data from the SQL Server Table by executing the following query.
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. Implement LSN value to restore deleted rows with the following command.
USE Databasename
GO
Step 7. To verify if the deleted records are recovered to the SQL Table database, you can use the following steps: Check the database logs to see if the deleted records are being stored. Then, verify if the deleted records are being recovered to the SQL Table database by running a query to check if the records are present in the database.
USE Databasename_Copy GO Select * from Table_name