How to Repair Corrupted SQL Server Database Manually?

If you are using MS SQL Server Database so corruption is very common issue there. But, as a database admin you have to aware about the solution of recovery. Also, you know about the reason of corruption, errors and prevention in SQL database.

Read the full details about the reason behind corruption, common errors and prevention methods in SQL database.

I noticed that many of SQL database’s user forget the SQL server database login password and hence, the user cannot access the SQL files. Learn here some manual tricks to recover the password.

How to recover SQL Server Database Password?

Now, come to the important point that how to repair the SQL Server Database, if the files get corrupted. There are many options to repair the corrupted SQL Server Database some are them are manual procedures and others are tools. Here, I share some important manual tactics to repair the corrupted files. But, before using any of them don’t forget to take a backup copy.

Method 1: Using Microsoft SQL Server Management Studio Express

Step 1: First of all, download the MS SQL Server Management Studio Express, according to the MS SQL database version.

Note:

• For Webtrends Analytics 8.5 and 8.5a, use MS SQL Server Express 2005:

http://www.microsoft.com/en-us/download/details.aspx?id=8961

• For Webtrends Analytics 9.2x and 8.7d, use MS SQL Server Express 2008:

http://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLManagementStudio%3bSQLManagementStudio

• You require Microsoft Web Platform first, before installation of Management Studio Express 2008.

Step 2: After installation of MS SQL Server Management Studio, open it by following the given steps. Go to Start -> Programs -> Microsoft SQL Server 2005/2008 -> SQL Server Management Studio Express.

Step 3: Use Windows Authentication, & log into the database.

Step 4: Now, expand Databases and then, right-click on wt_sched and select Properties -> Options.

Step 5: Scroll to the bottom in the pane and change Restrict Access to SINGLE_USER.

Step 6: Choose OK button and repeat #3 step for the wtMaster section of the database.

Step 7: Here, from the toolbar select New Query and paste the following below commands in the query pane.
dbcc checkdb(‘wtMaster’,REPAIR_REBUILD)
dbcc checkdb(‘wt_sched’,REPAIR_REBUILD)

Step 8: To repair the SQL database, choose the Execute button.

Step 9: Now, execute the steps 4-5 & change the databases back from SINGLE_USER to  MULTI_USER.
Note: Before starting the above process, ensure that no Webtrends services are started.

 

Method 2: Using Rebuild Wizard (Rebuildm.exe)

This is an important method to repair the master database with the help of Rebuild Wizard. The given wizard is located in the following place:

\Program Files\Microsoft SQL Server\80\Tools\BINN directory.

Step 1: Open the Rebuild Master by double-clicks on Rebuildm.exe.

Step 2: A Rebuild Master tab open and here, you will see the Collation Settings and location of data files.  Choose the database file & verified all other information. And, then click on Rebuild.  
Note: Take a copy of the source directory from the SQL CD to your hard drive.

Step 3: Now, you will see the confirmation message. Click on Yes button and after the completion of process, you will receive a message about the rebuild was successful.  Now, you have a new master database and you can restore the master database easily.

Step 4: Now, start a SQL Server in SINGLE USER mode by opening up a command prompt and typing the command sqlservr.exe –c -m from the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.

Step 5: From a backup, store the master database or you can also store it with the help of Query Analyzer or SQL Enterprise Manager.

Step 6: After restore, exit the SINGLE USER mode and restart SQL Server in NORMAL OPERATION mode.

Note:

• If the restore operation failed then try an alternative method. First, rebuild the master database and attach all of your databases by using Enterprise Manager or Query Analyzer.
• In Enterprise Manager, right-click on Databases and choose Attach Database.

 

Method 3: Using DBCC CHECKDB

Step 1: First, run DBCC CHECKDB on the corrupt database. Follow the query:

DBCC CHECKDB (Name of corrupt database)

Note: You can specify options such as no_infomsgs , infomsgs with DBCC CHECKDB.

Step 2: Now, see the index id.

Case 1:  If index id is greater than one then drop and recreate it.
Case 2: If index id is 0 or 1 then re-run DBCC CHECKDB with suitable repair options such as repair_fast, repair_rebuild,  repair_allow_data_loss.

DBCC CHECK (name of corrupt database, repair_fast)
DBCC CHECK (name of corrupt database, repair_rebuild)
DBCC CHECK (name of corrupt database, repair_allow_data_loss)

Step 3: To ensure the zero corruption, run DBCC CHECKDB and now, you will see a new message i.e

DBCC CHECKDB found 0 allocation errors and 0 consistency errors in ‘name of your corrupt database’.

Method 4: Using MS SQL Server Management Studio:

Step 1: Install and open the Microsoft SQL Server Management Studio.

Step 2: Connect it with your database. Click on New Query button.

Step 3: Now, in a New Query page paste the following SQL script and replace [YourDatabase] with your database’s name.

Step 4: Click Execute.

 

 Method 5:

Step 1: First, stop SQL Server instance and copy MDF and LDF files to the different location

Step 2: Now, delete the original MDF and LDF files and again, start the SQL Server instance and then, create a new database there.
Note: Use exactly same name and file names as old file.

Step 3: Now, stop SQL Server and overwrite the new created MDF and LDF.

Step 4: Once your database is online again then put it into an EMERGANCY mode and SINGULAR mode.

Step 5: Follow below mentioned code to execute DBCC CHCKDB and then check the repaired files.

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
 Note: REPAIR_ALLOW_DATA_LOSS means that some data might be lost, so always take a backup copy of files.

If all the above methods not helped you to repair your MS SQL database then, you need to focus on any third party MS SQL Database Recovery Tool. This tool is very effective and helps user to repair highly corrupted SQL database perfectly.