Remove suspect databases

I have an urgent problem I need help with!!
Last night we lost power and our SQL server kicked out without shutting down properly.
When the server came back this morning, the distribution and my_dbase databases were both marked as 'Suspect'.  I cant delete the my_dbase and recreate it as it is being used for replication.  I can remove the replication from the server becuase the distribution database is suspect.  Ive tried using sp_resetstatus on both, but that doent help.
I dont have a backup of the distribution database, but I can recreate the replications if I have to.  i do have a backup of the my_dbase database, so that can be restored OK.
Please help me fix my replication!!!
Who is Participating?
pedros7Connect With a Mentor Commented:
For some reason the above link didn't work when i clicked on it, so try:

Use emergency mode (also called bypass mode) to recover data when SQL Server marks a database suspect. Occasionally something (hardware glitches, software glitches, gamma rays, or whatever) causes SQL Server's ordinarily robust automatic recovery to fail. Of course, recovery failure is never a problem because everyone always has an accurate, up-to-date backup handy. Uh- huh. Right. But what if (hypothetically) you don't have a good backup?

Most of your data is probably still there on disk, but you can't get to it because SQL Server says the database is suspect. Setting the database status to emergency mode tells SQL Server to skip automatic recovery and lets you access the data. To get your data, use this script:

Sp_configure "allow updates", 1

Reconfigure with override


Update sysdatabases set status = -32768 where name = "BadDbName"

Sp_configure "allow updates", 0

Reconfigure with override


You might be able to use bulk copy program (bcp), simple SELECT commands, or DUMP TRANSACTION WITH NO_LOG to extract your data while the database is in emergency mode, but you won't be able to issue data modification commands. Get your data out as fast as you can before anything else can go wrong. Count your blessings if it's all there, because we can't guarantee that this technique will work in all cases. Call Microsoft if you're still stuck. The $150 service call is worth the money if Product Support can help you recover critical data. (See the Microsoft Knowledge Base article Q165918-- more information on this topic.)

Why might SQL Server mark your database suspect? For starters, try querying on suspect in SQL Server Books Online (BOL) in the MS SQL Server 6.5 Group in the Programs Start Menu. You'll be surprised how many hits you get. Granted, some of them are for the verb suspect, but you'll have dozens of hits on suspect as an adjective. For example, you'll find stored procedures such as sp_resetstatus and sp_mark suspect that turn a database's suspect flag off and on, respectively. Marking a database suspect is often the easiest way to drop it (if you can't drop it using SQL Enterprise Manager's Manage Databases or Server Manager windows) because you can drop suspect databases. Use the command

DBCC DBREPAIR(database_name, dropdb)

(Remember, though, that you can never drop master, model, or tempdb databases, or a database that's open or participating in replication.)

Also recognize that DBCC DBREPAIR is a command of last resort. Always try DROP DATABASE or sp_db remove first. DBCC DBREPAIR is for backward compatibility only.

Partha MandayamConnect With a Mentor Technical DirectorCommented:
maundedAuthor Commented:
Sorry about that..totally forgot I had this problem
To fix it, I had to delete the my_dbase then delete the distribution db, then restore my_dbase then recreate the replications.
Hope this might help someone else...  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.