A Database Administrator that works on the Microsoft SQL Server and handles a variety of database knows that a database is susceptible to many internal and external threats. Although the internal structure of the SQL Server protects the database and hides it from direct access, threats like accidental deletion, virus attack, Operating System abrupt shutdown, etc. can corrupt the data and make the table entries inaccessible.
So, it is advised to have regular backups of your SQL database stored on external drive, all Antivirus updated, scripts running under expert supervision, check on available storage and more such prevention measures to secure your MDF file from corruption in the future.
What are the causes behind the SQL Database corruption?
MDF is the primary file format that is created by the SQL Server to store the database and its tables. The MDF files are saved in the computer using the SQL Server at the dedicated location –
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
Because the MDF file is present in the computer system like the other windows-based file, so they can get corrupt due to any of the following reasons –
- The user has deleted the values from the tables especially the primary keys
- Some modifications and changes have made the data inaccessible
- There is corruption in the storage space where the MDF file is present
- If the SQL Server database file is stored within a compressed folder
- Network failure when the file was in use in the application
- The file header is corrupt, thus is further corrupting the MDF file
- The Disk Driver has corrupted many files including the MDF files
- If the table was in use and suddenly the SQL Server was shut down abruptly
- The random reasons behind the corruption are malware attack, spyware attack, power failure, abrupt operating system shutdown, disk driver failure
- Corruption in file system can corrupt the files and folders stored on system
All the corruptions affect the database separately and when the user runs the command, then it gives a different error message. Here are some prevalent error messages;
Error Message – 1
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2).
Error Message – 2
Could not load the definition for constraint ID %d in database ID %d. Run DBCC CHECKCATALOG to verify the integrity of the database.
Error Message – 3
Implicit conversion of %ls value to %ls cannot be performed because the resulting collation is unresolved due to collation conflict.
Error Message – 4
INSERT EXEC failed because the stored procedure altered the schema of the target table.
Error Message – 5
Invalid header value from a page. Run DBCC CHECKDB to check for a data corruption.
Error Message – 6
An internal error occurred while trying to convert between variable-length and fixed-length decimal formats. Run DBCC CHECKDB to check for any database corruption.
Error Message – 7
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
Error Message – 8
BACKUP detected corruption in the database log. Check the errorlog for more information.
How to Repair the corrupt MDF file?
Although, there is no robust free method to recover the tables from the corrupt MDF file. Restore from a recent backup file of the SQL Server can help but the chances of corruption in backup files stored on the same system are also high along with the original MDF file.
There is a DBDC command, that can look for the minor issues in the database and remove them (Repair MDF File). You need to run the command in the same database where the MDF file is present. Please follow the step given below;
Run the command;
The command will run the checkup for the whole database and bring out the information to you. In the given result, you need to check the index ID.
Condition 1 – If Index ID>1, then skip the command and run it again.
Condition 2 – If Index ID is either 0 or 1, then you need to run the command again with the help of helpful functions. The syntax to run the command is following;
DBDC CHECKDB (Database_Name, Repair_Rebuild)
DBDC CHECKDB (Database_Name, Repair_All_Data_Loss)
You need to run the command separately multiple times till the Index does not bring the SQL inconsistency error and shows zero allocation errors and consistency errors as the output message.
Conclusion
The free method to use the DBDC command is not a fulfilling method to remove any kind of corruption from the MDF files of SQL Server. It has many limitations like if the size of the MDF file is too large, then the process will be time taking and may not remove the corruption entirely.
If the Database Administrator forgets to run the command completely and skips a step, then it can further delete the data. If you do not want to lose the precious information saved in the database of SQL Server, then you should use the professional SQL Server Recovery software. The Recovery software can retrieve the complete information in a single cycle including the deleted SQL Server objects with original properties and lets you save the tables at a live SQL Server database.
