Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What do you do when you have a database that is marked as 'suspect'? Restore from the last backup? Please advise. Thank you!

share|improve this question
Always the best option is to restore the database from the available best backup. For more information read the following article which explain in detail how to repair a Suspect Database in SQL Server. – Smart DBA Jun 16 '12 at 13:10
One thing is to simply try to put it back online. That has worked for me in the past, and a good first step. ALTER DATABASE dbname SET ONLINE – Dustin Laine Sep 20 '12 at 16:20
If you have backup then its easiest otherwise emergency is the other option, and last option is you need to buy any 3rd party tool. I also faced this problem, but this article solved my problem. – Sharp Coders Oct 16 '12 at 18:46
up vote 34 down vote accepted

First thing is to make sure you DO NOT detach that database.

Restoring from the last known goodbackup is fine. Otherwise you will need to use the EMERGENCY repair mode (I am assuming you are running SQL 2005 or higher). Here are a couple of posts from Paul Randal on the subject. Read them both before you start taking any action.

share|improve this answer

I have written some guidance on this for 2 cases of suspect database: when you have lost the data file or log file. Please read the following:

share|improve this answer
So here's the thing: Stack Exchange doesn't work if all you post are links. What we need you to do is to summarize the content in the links, or I'll just be forced to delete your answer (and then you lose rep, and neither of us wants this to happen) – jcolebrand Jun 16 '12 at 22:18

Pretty much yes.

Generally it means the files are bollixed or missing or a disk error or some such (I've seen a bad sector cause this).

My steps:

  • Ensure all backups are there
  • Shut down SQL Server
  • chkdsk the disks used by SQL Server (hopefully not your C: of course)

Edit: I'll clarify my answer

  • if the data is important I'll have a backup
  • downtime while messing around with repairs and emergency mode is too long for me
share|improve this answer

From your question, it seems like you have a backup. Restoring the DB from a good backup will be the easiest and fastest way to get your DB operational and out of the suspect state.

share|improve this answer
But you'll loose data is you don't have the transaction logs. – mrdenny Apr 14 '11 at 3:13
EXEC sp_resetstatus [YourDatabase];


DBCC checkdb([YourDatabase])



share|improve this answer
This is a script, but it has no context and no explanation. For example, when should we use REPAIR_ALLOW_DATA_LOSS? Always? Only under some circumstances? What circumstances? What if sp_resetstatus fails for some reason? What if a database file is missing? And so forth. A reader does not need an encyclopedia answer, but the unadorned script offers nothing in the way of understanding. – RLF Sep 26 '13 at 13:18

protected by Community May 24 '14 at 20:18

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.