Click here to Skip to main content
11,645,889 members (63,890 online)
Click here to Skip to main content

How to Restore SQL Server 2005 Suspect Database

, 13 Sep 2007 CPOL 269.9K 46
Rate this:
Please Sign up or sign in to vote.
How to Restore SQL Server 2005 Suspect Database

Introduction

If your project's database is in suspect mode, then no transaction will take place until and unless you repair your database. That causes a show stopper for your up and running application. Here, you will find a way to get out of this.

Background

Your Database is in Suspect Mode. I guess, you haven't experienced this problem till now. But, if it comes to you and if the database is LIVE, then it's time to read this article to get out of this tension.

Using the Code

If you find your database in Suspect mode, then please keep your nerve strong. Just proceed step by step what I am written below. I think you will get out of this trouble. SQL Server 2005 introduced a new DB Status called Emergency. This mode can change the DB from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode. The steps are... After executing the script given below, you will get back your database in operational mode. Actually I have tried with two of my existing live systems and found no data loss.

Note: Obviously there are two more options available. Run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state. Here are a few things to bear in mind about emergency mode repair: it's a one-way operation. Anything it does cannot be rolled back or undone. If this worries you (if someone ever got into that state, then surely don't have the healthy concern about data that they should have in the first place) then make a copy of the damaged database before you run emergency mode repair.
As it's a one-way operation, you cannot wrap it in an explicit user-transaction.
It's the only repair option available in emergency mode - if you try to use REPAIR_REBUILD, then it won't work.

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Points of Interest

You will be happy that your database as well as the application are still in workable condition. Smile | :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Suvendu Banik
Program Manager
India India
Love to code

You may also be interested in...

Comments and Discussions

 
QuestionGreat piece of work Pin
Frankie-105898076-Jul-15 22:31
memberFrankie-105898076-Jul-15 22:31 
GeneralThank you so much!!! Pin
fedesanp11-Apr-14 7:41
memberfedesanp11-Apr-14 7:41 
QuestionRestore suspect database Pin
venkatarao sanni28-Mar-14 21:26
membervenkatarao sanni28-Mar-14 21:26 
QuestionThank You Pin
Member 855142811-Apr-13 21:58
memberMember 855142811-Apr-13 21:58 
QuestionThanks Pin
Dell.Simmons9-Apr-13 11:26
memberDell.Simmons9-Apr-13 11:26 
GeneralThanks Pin
Code4Tech5-Dec-12 1:11
memberCode4Tech5-Dec-12 1:11 
Questionhow to secure database in client system in desktop application Pin
Member 362275216-Nov-12 3:05
memberMember 362275216-Nov-12 3:05 
Question5 Stars here too! Pin
bortolo5-Oct-12 11:53
memberbortolo5-Oct-12 11:53 
QuestionVery good! Pin
Gilberto Campos25-Sep-12 12:36
memberGilberto Campos25-Sep-12 12:36 
GeneralMy vote of 5 Pin
Craig Errington20-Sep-12 5:45
memberCraig Errington20-Sep-12 5:45 
Questionit really works.. Pin
akshar shroff7-Jun-12 21:15
memberakshar shroff7-Jun-12 21:15 
GeneralMy vote of 5 Pin
ashutosh300731-May-12 22:23
memberashutosh300731-May-12 22:23 
GeneralMy vote 5 stars Pin
Norbert Meier17-Apr-12 23:23
memberNorbert Meier17-Apr-12 23:23 
GeneralMy vote of 5 Pin
Member 868252827-Feb-12 13:36
memberMember 868252827-Feb-12 13:36 
GeneralMy vote of 5 Pin
nawazish12khan2-Dec-11 22:24
membernawazish12khan2-Dec-11 22:24 
GeneralMy vote of 5 Pin
asrij8-Nov-11 17:48
memberasrij8-Nov-11 17:48 
AnswerPerfect solution. my vote is 10 of 5 Pin
mkwiatkowski8120-Oct-11 13:32
membermkwiatkowski8120-Oct-11 13:32 
GeneralMy vote of 5 Pin
Member 420927822-Sep-11 1:35
memberMember 420927822-Sep-11 1:35 
GeneralMy vote of 4 Pin
akhil khare15-Sep-11 21:39
memberakhil khare15-Sep-11 21:39 
QuestionThank you very much from Paris! It works for me ! Pin
Cybertipunch12-Jul-11 3:10
memberCybertipunch12-Jul-11 3:10 
GeneralI can't even begin to thank you enough Pin
BRM_Archer29-Jun-11 11:16
memberBRM_Archer29-Jun-11 11:16 
GeneralMy vote of 5 Pin
TheChange1-Jun-11 23:19
memberTheChange1-Jun-11 23:19 
GeneralThanks!!! Pin
Luis Alonso Ramos13-Apr-11 14:04
memberLuis Alonso Ramos13-Apr-11 14:04 
GeneralMy vote of 5 Pin
thirinwe30-Mar-11 19:00
memberthirinwe30-Mar-11 19:00 
Generalits working Pin
yvarjun21-Nov-10 20:20
memberyvarjun21-Nov-10 20:20 
GeneralThanks this workrd for me Pin
Vuyiswa Maseko24-Mar-10 22:24
memberVuyiswa Maseko24-Mar-10 22:24 
GeneralThis worked like a charm. Pin
Member 42593215-Dec-09 9:35
memberMember 42593215-Dec-09 9:35 
Generalbacking up a database in spsupect status Pin
Lido Taha14-Oct-09 22:47
memberLido Taha14-Oct-09 22:47 
Generalit restores the database but there is loss of data PLEASE HELP Pin
theodoraaaa28-Sep-09 5:11
membertheodoraaaa28-Sep-09 5:11 
GeneralIt worked for me Pin
mercyjms24-Sep-09 0:29
membermercyjms24-Sep-09 0:29 
GeneralThank you so much. Good work !!! Pin
tomket_666-Sep-09 8:43
membertomket_666-Sep-09 8:43 
GeneralThanks man it worked Pin
ashishshevale30-Aug-09 23:46
memberashishshevale30-Aug-09 23:46 
QuestionThis doesn't work on system databases Pin
srulyt22-Jun-09 1:55
membersrulyt22-Jun-09 1:55 
AnswerThanks to author Pin
chatakan6-Jul-09 23:44
memberchatakan6-Jul-09 23:44 
GeneralSaved me too. Pin
Shai Bar-Lev29-Apr-09 22:15
memberShai Bar-Lev29-Apr-09 22:15 
GeneralWorked for me. Pin
teza5918-Feb-09 14:08
memberteza5918-Feb-09 14:08 
GeneralTHANKS!!!! Pin
admlange17-Feb-09 23:16
memberadmlange17-Feb-09 23:16 
GeneralThank-you very much! Pin
MarkCorsi9-Jan-09 5:38
memberMarkCorsi9-Jan-09 5:38 
GeneralThanks, I owe you one big time! Pin
srulyt16-Nov-08 22:50
membersrulyt16-Nov-08 22:50 
QuestionDatabase Suspect Error Pin
sujithkumarsl9-Nov-08 23:00
membersujithkumarsl9-Nov-08 23:00 
GeneralThank you!!! Pin
Luis C. Lopez3-Nov-08 7:50
memberLuis C. Lopez3-Nov-08 7:50 
GeneralYou are a god amongst children Pin
Kosher18-Sep-08 15:16
memberKosher18-Sep-08 15:16 
GeneralFixed my problem Pin
eNocNRoll17-Jun-08 8:01
membereNocNRoll17-Jun-08 8:01 
GeneralRe: Fixed my problem Pin
Suvendu Banik22-Jun-08 23:58
memberSuvendu Banik22-Jun-08 23:58 
GeneralAnother success story! Pin
Kent Rothrock15-Jun-08 6:43
memberKent Rothrock15-Jun-08 6:43 
Generalrestore suspect database in SQL-2000 Pin
sqlbie2-Jun-08 6:38
membersqlbie2-Jun-08 6:38 
GeneralRe: restore suspect database in SQL-2000 Pin
Suvendu Banik2-Jun-08 17:53
memberSuvendu Banik2-Jun-08 17:53 
GeneralIt worked for me. Pin
KenThompson20-May-08 4:16
memberKenThompson20-May-08 4:16 
GeneralRe: It worked for me. Pin
Suvendu Banik2-Jun-08 17:55
memberSuvendu Banik2-Jun-08 17:55 
GeneralRe: It worked for me. Pin
Joginipalli Vamshi Krishna13-Jun-11 21:11
memberJoginipalli Vamshi Krishna13-Jun-11 21:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150731.1 | Last Updated 14 Sep 2007
Article Copyright 2007 by Suvendu Banik
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid