Friday, February 4, 2011

How to recover a suspect mode database

hi

recently i recovered a database from suspect mode. i thought to share the procedure that i have used with you guys.

How to make a suspect database up and running.

one of my databases is in suspect mode.

lets say the database name is avinash.

i have checked my database status in sys.databases table and found that it is in suspect mode.

select state_desc from sys.databases where name='avinash';

and the output was suspect

i made the database to emergency mode to repair the database.

alter database avinash set emergency;
now the database is in emergency mode and i am able to access the database but i can't do any thing except
to look at the data in the database.

then i have changed my database status to single user.

alter database avinash set single_user;

Now the database in single mode. but you will find that the beside the database name in the object explorer still the emergency is written. right, still the database is in emergency mode only, but just before executing the above statement you were able to access the database from object explorer and now you can not because the database is in single user mode and that single session is being used by you for executing the queries, so no other session will be opened.

now i have done the consistency check on my database.

dbcc checkdb()

i found that there were no consistency errors.

but i was not able to change my database to normal state.

then i have used the command. the reason for making the database single_user is for the below command.

dbcc checkdb(avinash,repair_allow_data_loss)

and the command was successful.

now i tried the command.

alter database avinash set multi_user;

now i found that my database is in normal state.