How to recover a suspect database on MS SQL 2005

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

Paul Kukiel

Developer

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Curabitur blandit tempus porttitor. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor.

2 comments:

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

    ReplyDelete

  2. A good solution to this problem with the server.
    In the continuation of the article I want to share with you the cloud server, which can be useful for you, ensuring high performance and reliable protection virtual data rooms for mergers and acquisitions

    ReplyDelete