SQL Data Recovery- How to Recover and Repair the Microsoft SQL Server Database

This small tutorial will help you understand how to verify and repair SQL Server Files to keep your precious database intact. After grasping the key point the user will be able to manually check the integrity of their database.

Assumption made:-

1. You have a MS SQL Server running.

2. Have complete administrative privilege.

To check if MDF File is Corrupt or not ?

Create an engine object by using the below mentioned query

• SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");

Where AdWks.sdf is your database path.

Call the “Verify” method to check the database integrity.

• if (false == engine.Verify()) {...}

The purpose of the verify method is to check whether the provided data is corrupted or not. If after applying the above query you get the following result you will be able to analyze its integrity.

1. True

The database need not any modification and is in a healthy state

2. False

False Implies that the database is corrupt and Needs to be repaired first for normal use.

Alternate Way to Check the Data Integrity

There is an alternate method to check the integrity of your database and should be done on regular basics to check the consistency and integrity of the database. SQL server provides some other queries for the purpose of data retrieval. There are two type of inconsistency logical and Physical, In case of former you don't have to worry much and the inconsistency in the database is a result of wrong use of commands and keywords. For example you may have provided a wrong foreign key which results in inconsistent output, so such type of logical inconsistency is easily managed. The main problem arises when there is Physical error in the database which generally is a result of faulty Hardware or outdated drives. So you should give your first priority in updating these two before performing any restoration in corrupt database. You can check the logical and physical integrity of the entire component by performing or running some basic commands. These commands will help you access the severity of your database, so that you can appropriate steps to bring it back to normal.

The following commands are illustrated with their working mechanism:-

1. The first command given in the picture help you to access the integrity of the database and perform all integrity check, "WITH NO_INFOMSGS" you can tell the application not to generate informational messages.

2. The second command checks the common hardware failure and if found in your system will help you take preemptive measure to tackle it. The command also checks if there are any checksum failure or any inconsistency in the pages.

You should make it a habit to perform these checking on regular basis so that you will be better prepared if you encountered with it.

Recovery Measures and Methods

If the application provides you with a True output then hurrah! You do not need to make any changes in your database and they are perfectly healthy but if however the result is false then you need to apply further programming to get them back to normal.

Recover the Corrupted SQL File

 You need to call the Repair method to fix the SQL Database. You can choose from the following different Repair methods based on the criticality of the database.

RepairOption.RecoverAllOrFail

In this method either all or none of the database will be recovered. This is the most effective way to recover and protect the database.

RepairOption.RecoverAllPossibleRows

In this the method tries to read as much data as possible and will try to restore even the corrupt database, but the major drawback is that the recovered database might not be fully corruption free.

RepairOption.DeleteCorruptedRows

 As the name suggests in this method the method will discard all the corrupt rows, if this method is invoked be prepared for a significant data loss.

RepairOption.RecoverCorruptedRows

 In this the method tries to recover as much of data corrupt data as possible, but one needs to understand that even if it is able to recover its element, there is no guarantee that the recovered data is corruption free.   

One Demo Example in this regard is (programmed in VB Script).

If everything is performed as mentioned the user may not face any difficulties in analyzing and performing the MS SQL data recovery. If the database is highly critical and needs every element to be recovered then you should try a SQL Data Recovery Software for 100% data recovery.