How to Repair SQL Server Database?
"The Article is discussed about the different techniques to repair Corrupted SQL database manually. Choose the best suitable method to perform SQL Database Recovery operation."
MS SQL Server Database corruption is the most common issue faced by the users of MS SQL Server. As the SQL Server Database Administrator you should have the sound knowledge of the recovery solutions with which you can easily repair the corrupted SQL server database. Also you should be aware about the various corruption reasons and prevention techniques.
How to Repair SQL Server Database
Now the main question is How to Repair Corrupted SQL Server database? There are several solutions with which you can easily perform the SQL Database Repair option. Here I have mentioned some manual solutions to recover the corrupted SQL Server Database. Before proceeding towards the solution please take the backup copy of your Database file.
Method 1: SQL Database Repair Using SQL Server Management Studio Express
Step 1: Download the SQL server Management Studio Express as per the version of your SQL database
- Use Microsoft SQL Server Express 2005 for Webtrends Analytics 8.5 & 8.5a.
- Use Microsoft SQL Server Express 2008 for Webtrends Analytics 9.2x and 8.7d.
- Microsoft Web Platform is required for the installation of Management Studio Express 2008.
Step 2: Launch SQL Server Management Studio Express and use Windows Authentication to Login into the database
Step 3: Expand Database >> Right Click on wt-sched >> Properties >> Options >> Scroll down to the bottom of the pane and Change the Restrict Access to SINGLE_USER >> OK
Step 4: Perform the same steps for wtMaster section.
Step 5: Select the New Query from the Toolbar and run the command given below and click on Execute button.
Step 6: Now execute the same steps for MULTI_USER.
Method 2: SQL Database Repair using the Rebuild Wizard (Rebuildm.exe)
With the help of this method you can easily repair and recover the master database. Follow the steps mentioned below to perform the same.
Step 1: Run the Rebuildm.exe that can be found in the location given below
\Program Files\Microsoft SQL Server\80\Tools\BINN directory
Step 2: In the Rebuild Master wizard you can see the Location of Data files and Collation settings. Select the data files and then click on Rebuild Button.
Important Point: Prepare a Copy of Source Directory from SQL CD to hard drive.
Step 3: Click on Yes once you received the confirmation message. This will rebuild the database and will give you a new Master Database that can be used to restore the Master Database easily.
Step 4: Launch SQL Server in SINGLE USER by using the command prompt and running the command sqlservr.exe –c –m from the path \Program Files\Microsoft SQL Server\MSSQL\BINN\
Step 5: Store the Master Database from Backup. You can also store the Database by using the SQL Enterprise Manager or Query Analyzer.
Step 6: Exit the SINGLE USER after restore and Restart your SQL Server in the NORMAL OPERATION mode.
What to do if restore Operation Fails
In such case you can rebuild master database and attach your databases by using Query Analyzer or Enterprise Manager.
Method 3: SQL Database Repair using DBCC CHECKDB
Step 1: Run the command DBCC CHECKDB on the Corrupted SQL database as shown below:
DBCC CHECKDB name-of-corrupted-database
Step 2: Now check and compare the index id
- If Index id > 1 then recreate it
- If index id = 0 or 1 then re-run DBCC CHECKDB using the suitable recovery option like repair_allow_data_loss, repair_fast, repair_rebuild
DBCC CHECK (name-of-corrupted-database, repair_fast)
DBCC CHECK (name-of-corrupted-database, repair_allow_data_loss)
DBCC CHECK (name-of-corrupted-database, repair_rebuild)
Step 3: Re-run the command DBCC CHECKDB to check if corruption is still there or not. This command should now show the message
DBCC CHECKDB found 0 allocation errors and 0 consistency errors in 'name of your corrupt database'.
Method 4: SQL Database Recovery using MS SQL Server Management Studio
Step 1: Install Microsoft SQL Server Management Studio.
Step 2: Connect database and then Click on the New Query button.
Now Run the script given below and replace [YourDatabase] with the name of your database
EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
Method 5: SQL Database Repair using third party SQL Database Recovery Tools
There are several third party SQL Database Recovery solutions that can be used to repair your corrupted database. Just Google for it and choose the best suitable SQL Database Repair Tool.
In this How To tutorial, I have described some methods for repairing SQL Server Database. Hope the above mentioned methods are helpful for you. In case if you have any query regarding the above mentioned topic or want to discuss anything then just start the discussion by commenting below.