Applicable to MS SQL Server 2017, 2016, 2014, 2012, 2008, etc.
How do I restore a deleted row or record in SQL Server
Have you ever met this problem while using SQL Server to manage your business or providing service to your clients? Deleting rows, table, page or column in SQL Server database is a common issue that most administrators may have or will have to face and figure out solutions to this problem.
Mostly, when a database or database components get deleted, MS SQL Server administrators will try to restore them from backups. But when you deleted database or tables without backups in SQL, what will you do? Qiling software has collected two solutions here to help you restore a single or multiple rows, table, page or column in SQL by using automated SQL Recovery tool or manual method with SLN.
Follow to see how to bring the missing data back and make everything of your business running back on the track.
An efficient way of bringing the lost SQL rows back is to apply automated SQL recovery tool for help. Indeed, powerful SQL recovery software can save your time and energy for restoring SQL Server data.
When you search online, remember to pick a 100% secure software provided by experienced companies. Qiling, with over a decade of data recovery experience, recently bring out its SQL recovery software with professional features to help you resolve SQL Server issues.
With it, you can effortlessly restore deleted records, including to undelete data in SQL, repair corrupted database, MDF file restore, etc. With only a few simple clicks, you'll bring the lost SQL Server rows back:
Step 1.Download and run Qiling MS SQL Recovery.
Step 2.Click the two dots (Browse button) to choose the target MDF file or click "Search" and select the desired file to repair from the list found below. Then, click "Repair" to start the repairing process.
Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.
Step 4. Click the "Export" button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to "Export to database", you can create a new database or export to an existing database.
After saving the deleted records including your lost rows as MDF file, you can import or save it back to the original location of your SQL Server database.
Then restart and run SQL Server again.
Another more complex method that you can try is to use LSN (Log Sequence Numbers) to check and restore the deleted rows.
Note: Be careful while you are following the below steps for deleted rows recovery in SQL.
Step 1. Check the numbers of present rows except for the deleted ones in SQL table
SELECT * FROM Table_name
Step 2. Use the below query to bring log back:
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. Gather information of deleted records from SQL Server table with below query:
USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS'
By doing so, get the Transaction ID of deleted records.
Step 4. Find the exact time when the records get deleted with the Transaction ID:
USE Databasename
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 the SQL Server table:
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. Check if deleted records including rows are recovered back to SQL Table database:
USE Databasename_Copy GO Select * from Table_name
Besides knowing the way to restore lost records or rows in SQL Server table database is not enough.
It's also important to back up the SQL database regularly. Automated SQL Server backup software is absolutely the best choice for you to create a full backup of your huge SQL Server database. If you are looking for a smart way to protect your SQL database, Qiling Backup Advanced Server is a second-to-none choice that you can't miss.