Tuesday, March 1, 2011

How to recover or repair a suspect database in sql server

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

You can run below cmd to get about all errors due to whihc db went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once you have enough info about errors then your next step should be Repair.If error is showing datafiles missing then check your datafile drive connectivity from storage side if its missing then you dont need to do anythig just contact someone from storage or one who manages storage in your env and ask them to check data file drive is healthy or not.

And if there is another issue then you can repair your database from below cmds:

SP_RESETSTATUS [DBNAME]
go
ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;


Put your db name in place of [DBNAME] in above script.

Note:-REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

No comments: