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 DatabaseName SET EMERGENCY;

Step 3:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

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
  • Run CHECKDB regularly

9 comments:

Unknown said...

great thanks it worked for me

Natalia said...

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

Natalia said...

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

Prett said...

Hello Sam,

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

ezLoan7 said...

Great post. Perfectly resovled an issue a customer was having.

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

jeje said...

nike shoes outlet
mbt shoes
off white shoes
oakley sunglasses
ralph lauren polo
ugg outlet
valentino shoes
red bottom shoes
jordan 4
ralph lauren polo