40

What do you do when you have a database that is marked as Suspect?

Restore from the last backup?

Please advise.

41

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.

Creating, detaching, re-attaching, and fixing a SUSPECT database

EMERGENCY-mode repair: the very, very last resort

5

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
5

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:

  • 5
    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
4

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.

  • 5
    But you'll loose data is you don't have the transaction logs. – mrdenny Apr 14 '11 at 3:13
0

My first advice is; never detach suspect database. Restoring the database from updated backup is helpful. If backup is not available or had issue then, EMERGENCY mode can be useful:

Set database into emergency mode:

ALTER DATABASE DB_NAME SET EMERGENCY

Now check the database inconsistencies with this:

DBCC CHECKDB (‘DB_NAME’)

DBCC CHECKDB Repair allow data loss option is last resort. The result may be data loss so, I do not suggest to run it.

Also check Reference 1 and reference 2

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.