I am going to share my experience about how I was able to recover my SQL database from SUSPECT mode.
Problem at hand
About a week ago, I was inserting some entries in the SQL database. However, the process was halted due to power failure and returned with the SQL server database in SUSPECT mode. So, I wasn’t able to access or use the database.
What I did to perform SQL Database Recovery?
At first, I checked the SQL error log on suspect pages and found that the transactional log file of the database had become corrupt. Following this, I performed several recovery attempts to extract data from the SQL database. I tried restoring the database from backup (which I haven’t tested for a while). Unfortunately, even the backup turned out to be corrupt. Now that was a great setback for me.
As last resort, I put the database into EMERGENCY mode to access the database marked as SUSPECT. Once the database was set in EMERGENCY state, I ran DBCC CHECKDB command which returned the repair_allow_data_loss option as the minimum repair level for the errors found. Using DBCC CHECKDB with repair_allow_data_loss option resulted in loss of transactional consistency.
The pressure to restore the database to its original state was building up, and so, I decided to opt for a third-party tool for the job. I remember reading on a few forums about Stellar Repair for MS SQL software, purpose-built to repair and recover corrupt SQL database files. I decided to give this SQL Recovery tool a try.
How Stellar Repair for MS SQL software works?
Software requirements were minimal:
Processor: Pentium Class
RAM: 2 GB
Free Space: 250 MB
OS: Windows 10/ 8.1/8.0/7/Vista/Windows Server 2012/2008 R2/2008
First, download and install the software on your system. Once installed, launch the software and follow these steps to perform SQL database recovery:
Step 1: The software will ask you to stop your SQL Server and copy the database to a different location. Restart the server and to repair the database copy, click OK.
From the software main screen, browse the database file (.mdf) file you want to repair in the Select Database window.
Note: If you are unaware of the database file location, use the Search option to locate the MDF file. Also, make sure to check the ‘Include Deleted Records’ checkbox if you wish to recover deleted objects as well.
Step 2: Once the file is selected, click Repair to start repairing the corrupted file. The software will display a progress bar to keep you informed about the time that Stellar Repair for MS SQL software takes to fix the db.
Step 3: Once the file is successfully repaired, a dialog box appears that tells that the selected SQL database files are repaired successfully.
Note: The software may fail to detect a heavily corrupt SQL database file. However, you can try selecting your MS SQL version manually from the dialog box as shown below:
Selecting an invalid version will prompt a message indicating that you have selected an inappropriate version of the file. So, make sure to choose the appropriate version to proceed with the repairing process.
Step 4: In the Repair Complete dialog box, click OK and the software will provide preview of recoverable database objects.
Step 5: To save the recovered data, click the Save button on Home menu. This will open up the Save Database dialog box. Fill in the required details to save the recovered copy of your SQL database (.mdf) file, and then click Save.
The database file will get saved at selected location.
Some key features of Stellar Repair for SQL:
- Allows users to save the SQL database scan results in order to resume recovery anytime later
- Previews recoverable objects
- Allows saving the repaired file in multiple file formats, like HTML, XLS, and CSV
- Handles all types of common SQL database corruption errors including 3414, 8942, clustered or non-clustered index corruption, database consistency error, and many more.
- Supports repairing a corrupt SQL db on the host Linux machine
I downloaded the free trial version of the software to ascertain its effectiveness. Once I was satisfied with the results, I activated its technician version.
Besides the technician version, two other licenses of the software are Stellar Repair for MS SQL, Platinum Edition and Stellar Toolkit for MS SQL.
Database downtime can be risky and can result in potential data loss. While there are manual solutions available to deal with SQL database errors, performing these solutions requires considerable effort and time. Also, data consistency may be at risk. But, you can restore the database with 100% integrity requiring minimal human intervention and time with the help of SQL server Stellar Repair for MS SQL software. It serves as a handy tool for all SQL that helps restore inaccessible or corrupt MS SQL Server database in just a few simple steps.
Here’s my rating of Stellar Repair for MS SQL (on a scale of 1 – 5):
|Installation||5||Takes only a few seconds to install|
|Ease of Use||5||The software UI is straightforward and simple-to-use|
|Features & Functionality||5||This is by far the best SQL recovery tool I have used with all the features required to repair and recover MS SQL database|
|Technical Support||5||Diligent 24*5 support team that makes best efforts to respond to queries without any delay|
8,263 views last month, 4 views today