How to repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode

Your database server won’t allow you to perform any operation on that database until the database is repaired.

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

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

and you are done.

You should keep one thing in mind while using the above queries that the repair mode used here , 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.

With SQL 2000 .........

First Step

"Suspect Mode" --> "Emergency Mode"

Open Enterprise Manager,

1. Right click onto your server node, - an SQL Server
   Properties dialog will popup,
2. Enable "Allow modifications to be made directly to the system catalogs" option

3. Now proceed to the sysdatabases table in the master
database, locate your database row and put 32768 into its status column

• Restart SQL Server and refresh Enterprise Manager
• Open Enterprise Manager - the
  database will be marked as "Emergency Mode"

you need to switch to an emergency mode, and you do it like this:

you right click the SUSPECT database,and choose properties.

all that is done from the Enterprise Manager.

click Server Settings (tab), and now click (un check) , the “Allow modifications

to be made directly to the system catalogs” and click OK.

now u need to open the “sysdatabases” table from the “master” database.

find the record of the “SUSPECT Ed” database, in the “status” column of that

record change the value to 32768.

by doing that u put the database to an emergency mode.

now do restart to you SQL server.

You click the START button Programs  Microsoft SQL Server  Import and Export Data and do a
copy from the SUSPECTED database to a totally new database.