Repair SQL Database Files

How to Repair corrupt SQL data files

 

SQL databases typically contain two types of data files, i.e. primary and secondary data file. The primary data (or .mdf) file is the beginning of your database and has links to other files in the database. All data files in the database other than the primary data file are known as the secondary data files. They have '.ndf' file name extension. However, corruption may occur in any of these data files, causing much downtime and in some cases, complete database inaccessibility.

 

SQL database corruption may result due to an instant power failure in the middle of a transaction or due to a hardware malfunction in the disk subsystem. Predominantly, hardware malfunction is the major cause of corruption. Other reasons that can incur corruption in the SQL database are database duplication, creating triggers on system tables, renaming the primary file group, etc. This corruption may remain obscure till the time you do not interact with the corrupt data. If corruption affects the backup files, you can never restore the database to its previous consistent state.

 

Some error messages received frequently in the event of SQL database corruption are listed below:

• Table error: Object ID 0, index ID 0, and page ID (1:105). The PageId in the page header = (0:0)
• Internal error. buffer provided to read column value is too small
• Conflict occurred in database'db_name', table 'table_name', column 'column_name'
• Memory or buffer error, space provided to read column is too small

 

To get away with these corruption problems, you should always have a good backup strategy in the first place. Using the most recent backup, you can perform MDF recovery and retrieve all your invaluable data stored in the database. However, it is usually experienced that maintaining the most updated backup is not always possible.

 

In such circumstances, you should use the DBCC CHECKDB command-line tool to repair data corruption. To check physical consistency of the database, run the following command:

 

'DBCC CHECKDB('neo') WITH PHYSICAL_ONLY; GO'

 

The two major repair options used with DBCC CHECKDB are:

• REPAIR_ALLOW_DATA_LOSS
  This option will usually try to repair all reported errors and might also cause some data loss. Therefore, you should use this option only as the last resort
• REPAIR_REBUILD:
  This option does not incur data loss. For example, if a non-clustered index is damaged, you can be easily repaired by rebuilding the index.

 

If the methods discussed above fail to resolve the problem, use Stellar Phoenix SQL Recovery. The software is embedded with several robust algorithms to extensively scan the corrupt database and perform a safe and reliable

MDF repair. The unique 'Preview' feature provides sneak-peek into the actual recovery results. Moreover, you can save the recovered queries, views, stored procedures, etc. in a separate text file that can be stored at any user-specified location in the system.

 

You need to follow the series of steps given below to perform precise recovery of your corrupt SQL database using Stellar Phoenix SQL Recovery:

• Main Interface of Stellar Phoenix SQL Recovery is shown below along with the 'Select File' dialog box. In the dialog box, click 'Browse' to select an SQL database or click 'Find MDF File' to search and locate all SQL databases in the system. Now, click 'Browse' at the bottom to specify the desired destination path for saving the recovered database. Click 'OK' to initiate the scan process.

  

   

• When the scan process is completed, all recoverable objects are displayed in a tree-view in the left pane. You can click an object to see its preview in the right-pane.

  

   

• You have an option to find a specific item in the list of recoverable items. Type the object name in the 'Find item' text box, check the 'Match case' or 'Match whole word' check-box, and then click 'Find'

  

   

• Select the objects you need to recover and click 'Start Repair' icon on the tool bar. The 'SERVER\INSTANCENAME' dialog box is displayed. Specify the SQL Server instance name and click 'OK'. When the process is completed, the recovered file will be saved at your specified location in the system.