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
Check the following link http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html
ReplyDeleteThanks Paul for the information about MySQL. Pretty useful and easily understandable
ReplyDeleteDot Net Training in Chennai | ASP.NET Training in Chennai