How to recover a suspect database on MS SQL 2005

by 4:38 PM 16 comments
We had a largeish DB server corrupt a disk and in the process cause one of the databases to become suspect.

This can be resolved by running the following:

EXEC sp_resetstatus 'DBName'
GO
ALTER DATABASE DBName SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBName', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBName SET MULTI_USER

GO

From: http://blogs.microsoft.co.il/blogs/dbaces/archive/2008/12/28/how-to-repair-sql-server-2005-suspect-database.aspx

However we had a database with a hyphen in the name eg: data-base-name

To get around this use " " around the data-base-name eg:


EXEC sp_resetstatus 'data-base-name'
GO
ALTER DATABASE "data-base-name" SET EMERGENCY
DBCC checkdb('data-base-name')
ALTER DATABASE "data-base-name" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('data-base-name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE "data-base-name" SET MULTI_USER

GO

16 comments:

  1. Check the following link http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html

    ReplyDelete
  2. Thanks Paul for the information about MySQL. Pretty useful and easily understandable

    Dot Net Training in Chennai | ASP.NET Training in Chennai

    ReplyDelete
  3. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.

    DevOps Training in Chennai

    ReplyDelete
  4. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Click here:
    angularjs training in chennai
    Click here:
    angularjs2 training in chennai
    Click here:
    angularjs4 Training in Chennai

    ReplyDelete
  5. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.
    Click here:
    Microsoft azure training in btm
    Click here:
    Microsoft azure training in rajajinagar

    ReplyDelete
  6. Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Blueprism training in Chennai

    Blueprism training in Bangalore

    Blueprism training in Pune

    Blueprism online training

    Blueprism training in tambaram

    ReplyDelete
  7. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Devops training in sholinganallur

    ReplyDelete
  8. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    best rpa training in chennai |
    rpa training in chennai |
    rpa training in bangalore
    rpa training in pune | rpa online training

    ReplyDelete
  9. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Data Science Training in Chennai | Data Science course in anna nagar
    Data Science course in chennai | Data science course in Bangalore
    Data Science course in marathahalli | Data Science course in btm

    ReplyDelete
  10. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.


    Best AWS Training in Marathahalli | AWS Training in Marathahalli

    Amazon Web Services Training in Anna Nagar, Chennai |Best AWS Training in Anna Nagar, Chennai

    AWS Training in Velachery | Best AWS Course in Velachery,Chennai

    Best AWS Training in Chennai | AWS Training Institutes |Chennai,Velachery

    ReplyDelete