How to recover a suspect database on MS SQL 2005

by Paul Kukiel 4:38 PM 140 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