Problem Scenario
Last week, I tried restoring a corrupted SQL database from the backup. However, the database restore process failed. I somehow managed to bring the database online, but a lot of records were deleted from the database.
After doing extensive research on Google, I came across a few solutions to recover the data. I tried to recover deleted data from the transaction log using an undocumented ‘fn_dblog’ function and log sequence numbers (LSNs) to the point at which the records were deleted. But, using this method returned the following error:
Msg 4335, Level 16, State 2, Line 1
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
To avoid any more downtime, I started looking for a quick solution to retrieve the deleted records.
Solution
I used Stellar Repair for MS SQL software to recover the deleted records from the database tables. The SQL Repair tool did an excellent job and helped me get back all the data without modifying the original database. Here are the steps that I followed to recover deleted records from the SQL table:
Step 1: Installed and launched Stellar Repair for MS SQL software. The software main interface window opened with an Instruction dialog box.
Step 2: I clicked OK and it opened a Select Database screen with Browse and Search options to select a SQL database MDF file.
Step 3: After selecting the file containing deleted records, I selected the “Include Deleted Records” checkbox, and then clicked the Repair button.
Step 4: I was presented with a “Select appropriate scan mode” dialog box with Standard scan and Advanced scan options. I decided to use the recommended standard scan option and clicked on OK to proceed with the repair process.
Step 5: The software displayed a status bar showing the repair progress.
Step 6: On completion of the repair process, a ‘Repair Complete’ message box appeared. I clicked on OK.
Step 7: A preview of all the recoverable tables, including the deleted ones, were listed in the left pane in a tree-like structure. Also, a log report was displayed at the bottom of the screen with complete details of the repair process. The report included details like the start and end of the repairing process, database version, database collation, database size, the total number of tables present in the database, etc.
But most importantly, under the total number of tables, I could find tables with deleted records, as shown in the image below.
I was able to identify deleted tables easily from tree view by finding the word “delete” added to the table name. Clicking on a table with deleted records, displayed all its details in the right pane.
Step 8: After validating the data accuracy, I selected all the recovered deleted records and clicked on the Save icon from the File menu.
Note: If you’ve downloaded the demo version of the SQL recovery tool, you will need to activate the licensed edition to save the recovered data.
Step 9: A ‘Save Database’ dialog box opened with multiple options to save the recovered data. MDF was selected as the default file format. However, you can also choose to save the data in CSV, HTML, or XLS file formats. I chose to save the file in a new database, but you may choose to save it to a live database. Further, I filled in the SQL Server details and selected the default location to save the file.
Step 10: After choosing the file saving options, I clicked on Save to begin the saving process. A “File Saved at the desired path” message appeared on the successful completion of the saving process.
After performing these steps, I opened SQL Server Management Studio (SSMS), right-clicked on Databases, and then click Refresh. The repaired database was added to the list of databases as Recovered_NewDatabase. I expanded the database tables and could find all the deleted tables and their data.
Verdict
SQL recovery tool from Stellar® is a reliable tool that can help recover deleted records from a table in SQL. It can also help extract data from corrupted MDF/NDF files without making any changes to the original data. I would recommend Stellar Repair for MS SQL software to any SQL user searching for a quick and efficient solution to getting back inaccessible or deleted records from a SQL database.