43

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

Restore from the last backup?

Please advise.

Oreo
  • 1,512
  • 9
  • 21
db7
  • 1,371
  • 4
  • 16
  • 20

4 Answers4

44

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

Trubs
  • 742
  • 1
  • 6
  • 14
SQLRockstar
  • 6,359
  • 26
  • 48
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
gbn
  • 69,809
  • 8
  • 163
  • 243
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:

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
yrushka
  • 1,954
  • 1
  • 16
  • 21
  • 6
    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.

StanleyJohns
  • 5,962
  • 2
  • 21
  • 44