Thursday, September 27, 2007

Restoring SQL Server 2005 Suspect Database

I had a SQL Server 2005 database in suspect mode,so i couldn't work on transaction until i repaired,while i am searching on the internet to resolve this problem i found an new Database status called Emergency introduced in SQL Server 2005.
This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.
Please follow the following steps to change the database status from suspect to emergency than to it's normal state:

EXEC sp_resetstatus 'DBname'

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER


I hope that will help you to get your database working

10 comments:

John said...

I had a SharePoint_Config go Suspect and these steps cleared up the problem. Fantastic.

khalid said...

It s Awesome!!

Eng. Ombiro said...

This is Superp. It worked for me. I work on sharepoint 2007 and MSSQL 2008, and after reinstallint MSSQL 2005 SP2 and SP3, the Sharepoint_config and SharedServices1_DB were suspect. Your solution worked pretty fine.

Leiyah said...

I did the same process. I was just thinking as to how long did the actual process took? I'm running the query for about 6 hours and it's still executing. Is there a possibility that the execution time would last for 24hours?

Bhaskar said...
This comment has been removed by the author.
Bhaskar said...

Thanks dude. It worked really like anything.

Also ppl u can find some more info on
http://www.aarat.com/sharepoint-config-database-suspect/

thanks.

Derrick said...

I've done that so many times.. :)

Mario said...

It worked like a charm. Thanks!!!!

Gopinath said...

It worked for me. Really great. Thanks a lot.

Ellis White said...

If you want to recover SQL Server database from suspect mode then you can use SQL recovery software which is capable to recover database from suspect mode and restore database to the SQL Server. http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html