Take the 2-minute tour ×
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. It's 100% free, no registration required.

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. mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html –  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
add comment

5 Answers 5

up vote 20 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.

http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx

share|improve this answer
add comment

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
1  
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
add comment

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
5  
But you'll loose data is you don't have the transaction logs. –  mrdenny Apr 14 '11 at 3:13
add comment

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
add comment

EXEC sp_resetstatus [YourDatabase];

ALTER DATABASE [YourDatabase] SET EMERGENCY

DBCC checkdb([YourDatabase])

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [YourDatabase] SET MULTI_USER

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
add comment

protected by Community May 24 at 20:18

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

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.