Wednesday, August 3, 2011

Recover SharePoint Site from suspect database

 When Central Administration in SharePoint 2010 stops abruptly and its Content/Config database switches itself automatically to Suspect mode for no apparent reason, that too after a very stressful and super-hard working day for me.


To recover your sharepoint site from suspect database use following steps:-


1. Go to your MSSQL\Data files that reside under: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

2. Identify your Central Admin Content Database name in my case it got screwed up and was in the suspect: mode: so this is my content database name WSS_Content_13885.

NOTE: Please take a backup of the corrupted .mdf and .ldf files, before following other steps

3. Open your SQL Server Management Studio ->, New Query and it opens up your SQL Query editor, copy and paste the query below and change the highlighted to your database name

--Verify whether Database has any issues
EXEC sp_resetstatus
"WSS_Content_13885"

---Alter database and put it on Emergency Mode
ALTER DATABASE "
WSS_Content_13885" SET EMERGENCY
DBCC checkdb
('WSS_Content_13885')

--Set the database in the Single User mode
ALTER DATABASE "
WSS_Content_13885" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Repair the database and allow data loss
DBCC CheckDB
('WSS_Content_13885',REPAIR_ALLOW_DATA_LOSS)

--Set the database back to Multi-User mode
ALTER DATABASE
"WSS_Content_13885" SET MULTI_USER

--Ensure Database is reset
EXEC sp_resetstatus
,WSS_Content_13885'.

Execute all the commands in your SQL Query Editor and there you go, Go back to your SQL Management Studio and you can see that the (Suspect) mode issue against the Content/Config database is fixed and the database got fully repaired and restored.

To verify the same, hit your Central Administration and it starts working just fine...

6 comments:

  1. Hello Bhusan,

    I have read your post, at last you have mentioned "database got fully repaired & restored". As I know command 'DBCC CHECKDB with repair_allow_data_loss' tries to repair & restore your corrupt database with minimum loss not restore full database. For full recovery, you can restore from backup or any 3rd party SQL recovery software.

    Thanks,
    Raj
    SQL server DBA

    ReplyDelete
  2. Hello Bhushan,
    It worked like charm for me You saved my day !! Thanks a ton I am not a DB guy. It would be helpful if you could give me some details on why to set it on single mode and multi user mode a description of each command.

    But Thanks a ton again !!

    Avinash Reddy

    ReplyDelete
  3. I like what you guys are up too. Such intelligent work and reporting!Keep up the excellent works guys I have incorporated
    SharePoint 2013 Training

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Excellent post! But, there is no guarantee that the REPAIR_ALLOW_DATA_LOSS will fix this problem as the command is used at the very last resort. So, it is better to have a good backup plan for your database or have an alternate solution like SharePoint Recovery Tool to repair corrupt SharePoint database.

    ReplyDelete
  6. SharePoint Database Recovery utility ensures providing the results you desire with less possible effort. It features the function with which you can attach the database file recovered on the web application.

    ReplyDelete