Restore corrupt MDF files with SQL database repair tool

Sarthak Poswal

Updated On - June 13, 2024

SQL Server uses the MDF (Master Data File) to store user’s data. Data inserted or updated in a database is saved into the MDF file. This MDF file is also responsible for holding the database schema, which defines the structure of the table. Even the read query made by user needs MDF file to retrieve the data. So, you can understand how crucial this file is for a database.

Every organization, large or small, relies on databases for regular data updates and storing new records. The inability to access the database could be fatal to an organization’s workflow and productivity and may even impact it financially and reputationally.

The corruption of MDF files is not a very frequent phenomenon. However, nothing is prone to corruption. The damage could be the result of various factors like:

• Hardware failure
• Virus or malware attacks.
• Improper handling and shutdown of system.
• System sudden crash
• Restoring data from the MDF file.

We will be understanding 2 manual ways of restoring data and one advanced automatic tool to achieve the same result. If your database is only suffering from minor consistency issues, then you can repair the database using the dbcc checkdb command.

One of the simplest and quickest ways is to use your database backup. If you are in possession of a good and consistent backup, then you can restore your data from it.

But it will only restore data that is present in the backup. Any update or insert operation performed on the database post-backup will not be recovered. That data will be lost permanently.

In case backup is missing you can try other ways to recover data without backup file.

To restore the database, run the following query in the SQL Server Management Studio:

RESTORE DATABASE YourDatabaseName
FROM DISK =’FULL_PATH_OF_BACKUPFILE’
WITH REPLACE;

Users may check the database for minor issues using the dbcc checkdb command.

Executing dbcc checkdb query:

1. Set the database status to emergency mode.

   ALTER DATABASE [Your_DB_Name] SET EMERGENCY

2. Execute the query:

   DBCC CHECKDB (Your_DB_Name)

The output displayed will show information for any inconsistencies or errors found. It also mentions the minimum repair level needed to fix the issue. It can show two levels, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS.

The REPAIR_REBULD can fix minor inconsistencies, and most of the time the system will ask for REPAIR_ALLOW_DATA_LOSS as the minimum level.
Depending upon the output, the user can proceed with running the query.

1. Set the database to single-user mode.

   ALTER DATABASE YourDatabaseName SET SINGLE_USER

2. Run the repair query.

   DBCC CHECKDB (‘YourDatabaseName’, REPAIR_REBUILD);
   (For minimum level repair = repair_rebuild)

   OR

   DBCC CHECKDB (‘YourDatabaseName’, REPAIR_ALLOW_DATA_LOSS);
   (For minimum level repair = repair_allow_data_loss)

3. Set the database back to multi-user mode.

   ALTER DATABASE YourDatabaseName SET MULTI_USER

4. After the repair, run DBCC CHECKDB again to ensure that all consistency errors have been resolved.

Note: The repair_allow_with_data_loss will delete pages from the database which means data loss. So, 100% data restoration is not possible with this repair option. Also, it is not reliable for files with severe corruption.

Recoveryfix for SQL Database Recovery is capable of 100% data restoration with complete integrity. The manual methods discussed above are complicated and may even lead to permanent data loss.

This tool offers unparallel functionalities, some of which are:

• Embedded with innovative algorithms for meticulous recovery
• Swiftly recover objects, including tables, views, stored procedures, indexes, rules, and user-defined data types.
• Compatible with all SQL versions.
• Maintains complete data integrity and original hierarchy.

Restoring database from MDF file with the SQL tool.

It is quite easy to use Recoveryfix for SQL Database Recovery tool. Steps in detail are given below for better guidance:

1. Open Recoveryfix for SQL Database Recovery.
2. Click Browse, select the corrupt/damaged MDF database file and click Next.

Note: You can change the SQL Server Database version by clicking on the drop-down list.

3. Wait until the scan process completes:

Note: In case, if you wish to select another MDF database file, click on the Stop button and select the other MDF file.

4. Upon completion of the file scan, Recoveryfix for SQL Database Recovery will load all database objects and list them in a tree structure as below.
   Checkmark to select database objects to recover and click Next.
5. Select Saving Mode.

   For demonstration, we’ve selected “Save into live SQL instance” option. It’ll recover data into the newly created database.

   Select Authentication mode and, input authentication details and click.

   

Note: You can choose between Windows or Server Authentication mode as per your preference.

6. Wait for the process to complete in case. If you made any mistake or wish to make any adjustment, click on Stop button.
7. Upon successful MDF database file repair & recovery, Recoveryfix for SQL Database Recovery will show objects saved as below, click OK.

Note: For future reference, you may save the process log by clicking on the Save button.

Conclusion

Timely recovering of data and restoring the database to its original functionality is critical for an organization’s workflow. To recover data efficiently and quickly, use the expert-recommended advanced tool Recoveryfix for SQL Database Recovery. It is powered by advanced algorithms to deliver reliable and satisfactory results. It provides a minimalistic user interface which is accessible to users who may get overwhelmed by the manual technical methods.