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

9 comments:

Unknown said...

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

Unknown said...

It s Awesome!!

Eng 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.

Unknown 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?

Unknown said...
This comment has been removed by the author.
Unknown 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.

Unknown 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.