How to recover a suspect database on MS SQL 2005

by 4:38 PM 8 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

8 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