It is not unusual for one to encounter a damaged MDF file of MS SQL Server database. The simple reason being, it is prone to getting corrupt, and any discrepancy in handling the same can result in a corrupt MDF file. When this happens, all the data stored in the database becomes inaccessible, therefore, leading to data loss. In such situations, what becomes essential is to repair corrupt MDF file of the SQL Server database storage.
This Blog will describe how to perform the repair process. Also, it will also give you the in-depth knowledge of the MDF file, reasons as to why it gets corrupt, identifies some tools to repair it, and also the most efficient resolution in case the free repair methods fail to produce healthy SQL Server database file stored in MDF format. Let’s get started!
What is an MDF file?
The MDF file housed in the Microsoft SQL Server, a relational database management system, is the primary data file of the database that stores all data. Therefore, you can also refer it as the main or master database file of the SQL Server. Each SQL Server database would contain at least one MDF file.
It stores components such as Indexes, XML Indexes, Views, Tables, Triggers, Stored Procedures, Rules, User Defined Functions, Keys and data types, sparse columns, column set property, and file stream data types. The MDF file can be summed up as the primary element for administering the SQL database!
Why does the MDF File become corrupt?
The MDF file of SQL Server database gets corrupt because it has the least resistant to corruption issues. Several causes are responsible for damaging the server’s primary file type.
- Sudden power failure.
- Bugs in the server itself.
- Faulty Operating System.
- Unexpected shutdown of the system,
- Problem with the hard drive, and virus outbreaks, etc.
Thus, reasons for your MDF file turning corrupt can be anything from hardware to software malfunctioning!
Read a discussion on Spiceworks: What is database corruption? And how do you deal with it?
In-built tools to repair the damaged MDF file
Here, you will get to know about some tools that can repair corrupt MDF files of SQL Server and consequently, make the stored data available. In fact, these tools are a series of statements (commands) in Transact-SQL programming language and are known as Database Console Commands (DBCC). Earlier, DBCC was known as Database Consistency Checker. The function of these commands in DBCC is to test the physical and logical uniformity of the SQL Server database files as well as fix the troubling issues that persists.
DBCC CHECKDB is a statement through which you can check the logical and physical integrity of all the objects in a specific SQL Server database. You can do so by executing the following operations sequentially:
- Running DBCC CHECKALLOC command in the database.
- Running DBCC CHECKCATALOG command in the database.
- Running DBCC CHECKTABLE on each view and table in the database.
- Authenticating the content of each indexed view available in the database.
- Authenticating link-level constancy among table metadata, file system directories, and files when storing varbinary (max) data using FILESTREAM in the file system.
- Authenticating the Service Broker data in the database.
On completion of the above steps, if the utility finds any corruption issues or errors it recommends users to make use of several Repair options for fixing the troublesome issues. The Repair options are:
- REPAIR_FAST – It maintains syntax for backward compatibility only; no repair actions are performed in actual. The syntax for this Repair option is:
DBCC CHECKDB (‘DB Name’, REPAIR_FAST)
- REPAIR_REBUILD – This repair option executes repair procedure that scarcely has possibilities of data loss. This can perform quick repairs such as repair of the missing rows in non-clustered indexes, and even time-consuming repairs such as the rebuilding of indexes. The syntax is:
DBCC CHECKDB (‘DB Name’, REPAIR_REBUILD)
Note – It (REPAIR_REBUILD) does not fix errors that include FILESTREAM data.
- REPAIR_ALLOW_DATA_LOSS – This command makes an effort to fix all the errors that are reported. However, it can cause loss of data as stated in the repair command itself. The syntax is:
DBCC CHECKDB (‘DB Name’, REPAIR_ALLOW_DATA_LOSS)
Note – You may end up losing data after performing repair operation with REPAIR_ALLOW_DATA_LOSS. Therefore, you should always use it as the last resort if none of the other repair options works.
Few Checks with Built-in Repair Options
Care should be taken while using these three free Repair options for the repair of damaged SQL Server MDF file.
- The first and foremost requirement is that the particular database should be in a single-user mode to be able to run either of the three Repair commands. When a user does not set the database in single user mode then, the following error message occurs:
“Repair Statement is not processed. Database needs to be in Single user mode.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
You can alter the SQL database into single user mode by the following command:
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Secondly, the syntax to execute the DBCC CHECKDB repair commands must be used correctly. It is only then that the discrepancies shall get fixed, resulting in an entirely healthy database file.
- The DBCC repair statements are not validated memory-optimized tables. In such cases, i.e. if integrity issues occur in a memory-optimized table, you must restore the backup to gain access to the data in MDF file, provided the backup file is not in a damaged state.
The DBCC DBCHECK repair tools failed! What next?
If the built-in DBCC DBCHECK fails to repair the corrupt MDF file, the next thing that you can do is to run any external SQL recovery software. However, it should have the capability to outshine the manual methods and achieve what is expected out of it. Stellar Phoenix SQL Database Repair software can be used for repairing the corrupt MDF files as well as extract the data stored within it.
How to Repair Corrupt MDF File, using Stellar Phoenix SQL Database Repair Tool
MS SQL Server Recovery to Get Back Your Data