Fail to attach MDF file in the SQL Server!

Published by Jacob Martin on December 4, 2013

SQL Server database consists of three types of files namely primary, secondary and transaction log files. Primary and secondary data files can store data in the database while transaction log files helps to hold transaction information that is used to recovering data at the time of disaster. While creating a database in the MS SQL Server, it automatically creates two types of files the primary or MDF and log or LDF files. SQL Server treats the MDF file as primary database file to store all the data. Again each MDF file has a LDF file that is associated with MDF files.

There are many reasons that lets the SQL file to corrupt for example:

“When I tried to attach MDF files in newly built SQL Server. Whenever I tried to attach MDF files it regularly generated an error “Is not a primary database file, Microsoft SQL Server, Error: 5171”. It’s seemed as if MDF files got damaged due to unreadable header.”

Due to corruption of SQL database some common error messages generated are

• Server can’t find the requested database file
•  The .mdf file is missing and needs to be restored.
•  The process could not execute ‘sp_replcmds’ on server
•   On the changes table was working but the .frm is locked.
•   Corruption error of indexes, stored procedures, database integrity and triggers should be there.

The reason why SQL Server shows such errors may be due to MDF file may become victimized to corruption which may lead to data inaccessibility. The MDF files are very sensitive database so it easily gets corrupted due to different possible reasons. You cannot fix and repair all the issues with SQL database. It the MDF files gets infected it is unable to retrieve tables, triggers, views, rows, columns, database attributes of the SQL database.

Reasons behind MDF file corruption

•   MDF files may get corrupted due to malfunctioning of either the hardware or the software.
•    Error that generated within the storage media.
•   Improper shutdown or termination of SQL database.
•   Invalid file header in the SQL Server.
•   Unintentional or accide4ntal deletion of SQL database.
•  Large size of SQL Database.
•   Network failure.
•    Virus attack on the computer or the database
•   Inaccessibility of portioning of drivers.
• Media corruption or damage.
• Hardware problem and malfunctioning of software.

These reasons lead to corruption of SQL files and database which can be more destructive enough to shutdown of the whole system. The entire system level data are stored in the MDF database so any damage to these files missing, damage or unavailability of data.

How to repair corrupt MDF files

Microsoft provides free utility for fixing the SQL Database issues which is DBCC CHECKDB. DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified SQL Database. If the SQL database suffers from corruption than the DBCC CHECKDB command will find the exact problem behind the corruption and will repair it. But sometimes it gets fail to resolve the SQL database errors and for these situations using a  SQL MDF Repair Tool is the only solution to get rid of this problem and helps you to retain the MDF file quickly and efficiently. This powerful recovery tool will help to fix all the possible errors of SQL database and enable accessibility to even crucial data. This recovery software helps in-

•  Database components repair- Recovers and repairs all the components of SQL database including views, indexes, stored procedures, tables and triggers, rules etc.
•   Resolves from SQL Database Corruption errors- This tool resolves the database from number of corruption issues and errors like schema corruption, consistency corruption, and clustered index leaf level corruption and so on.
•  Corrupted SQL Server Database Repair- It scans the database and repairs the corrupted database and brings it back to its consistent state.
• MDF and NDF files objects restoration- This tool is ideal for repairing primary and secondary data files of the SQL Server.
•  Recovers Row and Page compressed Data- Repairs and recovers back to its original state all the SQL tables with ROW and PAGE compression schema.
•   Generates Preview of database objects- Once scanning finishes it generates preview of all the data that is recovered in form of tree like structure and lists all the tables in a sorted manner.

Steps to repair corrupt MDF files

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

380 Total Views 1 Views Today