Monday, 22 February 2010

How to recover a Database in suspect mode

I was struck with a database which is on suspect mode and need to recover it without any recent backups. I searched across the net and found these steps which are quite useful

Step 1:Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 2:Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.ALTER DATABASE DatabaseNameSET EMERGENCY;

Step 4:Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

There are some best (simple) practices which prevents us from such failures. Below are some of them

Backup your data frequently ( daily or once in two days)

Have multiple backups. Move the backups to external drives or tapes frequently

Validate that your backups are good by doing trial restores to alternate server

13 comments:

Great it worked for me also. I was looking for a solution and have finally found it on your blog. Thank you so much for sharing all the points to recover a database. You have saved my time and helped me a lot as I was fed up of searching for an appropriate solution. Cheers !sap erp system

Great it worked for me also. I was looking for a solution and have finally found it on your blog. Thank you so much for sharing all the points to recover a database. You have saved my time and helped me a lot as I was fed up of searching for an appropriate solution. Cheers !sap erp system

I have also stuck with this error. This is one of the most common error in SQL Server Database. I read many blog posts, forums & articles about this error. In many cases, the solution that you provided is not surely resolve this error. I was also unable to recover suspected SQL Server database by given solution. At that time I read this article & resolve the error: Repair Suspected SQL Server