Events like database corruption, server crash, or hardware failure make the database corrupt and inaccessible. In this case, you can restore the recent backup to recover the SQL database. However, you may fail to recover the database if the backup file is corrupted or the database is in suspect mode. Inability to restore the backup increases risk of losing mission-critical data for your organization.
In This Article
Stellar Repair for MS SQL software helps you repair corrupt SQL database (MDF and NDF) files. It recovers database components, tables, triggers, indexes, keys, rules, stored procedures, and more.
A file with .mdf extension is a Master Database File used by Microsoft SQL Server to store user data. It is of prime importance as all the data is stored in this file. The MDF file stores users’ data in relational databases in the form of columns, rows, fields, indexes, views, and tables.
A file with .ndf extension is a secondary database file used by Microsoft SQL Server to store user data. NDF is a secondary storage file because SQL server stores user-specified data in a primary storage file known as MDF. NDF data file is optional and is user-defined to manage data storage in case the primary MDF file uses all the allocated space. It is usually stored on a separate disk and can spread to multiple storage devices. The presence of MDF files is necessary in order to open NDF files.
Let’s consider the following three scenarios where the SQL database can become corrupted or inaccessible:
1) When a SQL Server suspects the primary filegroup of the database to be damaged or if the database file is missing, the database status is set to ‘Suspect’. When the SQL database goes into the suspect mode, it becomes inaccessible. In such a situation, you will neither be able to connect to the database nor recover it during server startup.
2) When it comes to extracting data from an SQL database, database administrators (DBA) rely on the .bak file to restore the data. Unfortunately, the file can become corrupt and makes it difficult for DBA to recover the database. SQL Server database backup can become corrupted due to reasons like abrupt system shutdown, virus attack, software bugs, etc.
3) DBCC CHECKDB is executed on the SQL Server database to check the physical and logical integrity of the database’s integral components. These objects include tables, indexes, keys, stored procedures, triggers, etc. DBCC CHECKDB also repairs and checks the structure of all the database tables and indexes. In case any of these checks fail while executing DBCC, and you might expect data loss.
With Stellar Repair for MS SQL, you can repair all types of SQL database corruptions scenarios.
To follow this article, you need to have the following:
1) Any supported SQL version (SQL 2019, 2017, 2016, 2014, 2012, 2008, or older versions).
2) Stellar Repair for MS SQL free trial version. You can download it for free without registration (6MB). At the time of this writing, we are running the latest Stellar Repair for MS SQL version 10.0.0.0.
Install Stellar Repair for MS SQL
Once you’ve downloaded Stellar Repair for MS SQL, you can follow the simple steps below to install it:
Launch the StellarRepairforMSSQL.exe executable file. On the Welcome to the Stellar Repair for MS SQL Setup Wizard, click Next > to continue.
Accept the License Agreement and click Next > to continue.
On the Select Destination Location page, select where should Stellar Repair for MS SQL be installed? To continue, click Next >.
Select where the Setup should place the program’s shortcut. To continue, click Next >. If you would like to select a different folder, click Browse.
Select the additional tasks you would like Setup to perform while installing Stellar Repair for MS SQL. Click Next > to continue.
Once you are ready, click Install to begin installing Stellar Repair for MS SQL on your machine.
Once the installation is completed successfully, you are ready to Launch Stellar Repair for MS SQL. Click Finish to exit Setup.
If you have an activation/license key, you can install it by selecting the Activation tab as shown in the figure below.
Recover and Repair MS SQL Database
In this section, we will show you how to repair a SQL database in suspect mode.
When the SQL server suspects the primary filegroup of the database (.mdf) file to be damaged or if the database file is missing, the database status is set to ‘Suspect’ mode as shown in the figure below.
Also, there is a wide range of errors that could result in SQL database in suspect mode. Some of these errors are listed below:
> The system fails to open the device where the data or log file of the SQL server resides.
> SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
> SQL Server tries to open a database, and the file belonging to that database is already open by anti-virus software installed on your system.
> The database is terminated abnormally.
> Lack of disk space.
> SQL cannot complete a rollback or roll forward operation.
> Database files are being held by the operating system, third-party backup software, etc.
A SQL database is considered to be damaged if one or more of its core files are in an inconsistent state. Depending on how the damage is, the DB is marked with different states. Some of these states are:
> Online – If one of the data files is damaged when executing a query or some other operation, the database will remain online and accessible.
> Suspect – If a database cannot be recovered during the startup of SQL Server, the database is marked as Suspect.
> Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the DB in the ‘Recovery Pending’ state. This is different from the SUSPECT mode state because it cannot be said that recovery is going to fail – it just hasn’t started yet.
In this case, you can try first restoring the database from a good backup. If the backup is not available, then you could proceed with the manual steps documented here to repair a SQL database marked in suspect mode.
If your server database file has turned severely corrupt, the manual steps may fail to revive the database. At this point, you need to try restoring the database by using Stellar Repair for MS SQL. Take the following steps:
1) First make sure to close and stop the server SQL instance before running Stellar Repair for MS SQL software.
# Stop SQL Service Instance Stop-Service -DisplayName "SQL Server Instance Name" -Force
2) Launch Stellar Repair for MS SQL, then from the Select Database window, choose to Browse or Find to select the SQL database file (.mdf) of the suspect database as shown in the figure below.
3) Once the (.mdf) file is selected, click Repair as shown in the figure below. Please make sure to uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.
4) Next, select which scan works best. I’d start with the Standard Scan (Recommended) as shown in the figure below. It worked most of the time for me. Click OK to continue.
5) After it runs, you’re presented with, basically, a recovered database as shown in the confirmation message below. Click OK to continue.
6) Next, preview the repaired MDF file for recoverable SQL server database objects as shown in the figure below.
7) Since you’re working with data files, not actual databases, you now have to “Save” the repaired and recovered .mdf file. As shown in the figure below, you can also export the data and database to CSV, HTML, or XLS. You can also choose “Live Database“, which means simply importing all the database objects and data that you repaired into a new database.
In this example, we are just going to want to recreate the .mdf file by selecting “New Database“. This option will create a new MS SQL database and save all the data from the corrupt MS SQL database to the newly created database. Click Next to continue.
8) Next, select the Server Name\Instance Name, choose the Authentication method, and finally set the Location to where you want to create the new database. Click Next to continue.
Please make sure to start the server SQL instance first before saving the database.
# Start SQL Service Instance Start-Service -DisplayName "SQL Server Instance Name"
9) Finally, on the Save Mode window, you can choose between Fast Saving and Standard Saving as shown in the figure below. The default is Fast Saving, this option is advisable for large databases, it saves the data first and then saves indexes. On the other hand, Standard Saving is a convenient way of saving the repaired database, it saves the data and the indexes at the same time. Click Save to continue.
10) Depending on your database size, saving the tables to a new database will take some time to complete. Once is completed, you will see the status as shown in the figure below. Click OK.
The only thing that we didn’t like was when we went to create a new database, it didn’t let me pick a new name. The recovered database was named ‘Recovered_SQLInstanceName$OriginalDatabaseName’ as shown in the figure below. We hope that the Stellar team will add this small enhancement in the upcoming release to be able to name the new database.
Additionally, Stellar for MS SQL will repair corrupt MDF and NDF files for SQL databases in Suspect and in Recovery Pending state. It recovers tables, triggers, keys, indexes, stored procedures, defaults, rules, schema, and provides multiple saving options to save the repaired database including MS SQL (MDF), CSV, HTML, and XLS.
That’s it there you have it!
In this article, we showed you how to repair and restore a corrupt SQL Server Database with Stellar Repair for MS SQL Software.
There are many reasons which cause corruption in SQL Server database transaction log files such as hardware failure, large-sized LDF files, etc. And a corrupt log file can cause the database inaccessible. Therefore, in this article, we have discussed the different scenarios and manual approaches to repairing SQL Server databases. Moreover, sometimes manual approaches do not work or cause data loss, so for this, the best option to repair the database is by using SQL Recovery Software.
Based on my experience, we highly recommend Stellar Repair for MS SQL which is a great piece of software. It literally does what it says on the outside of the tin: repairs corrupted databases and recovers deleted data.
We rate this software with five stars.
Thank you for reading my blog.
If you have any questions or feedback, please leave a comment.