Repair SQL Server Database marked as Suspect or Corrupted

There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

Reset the suspect flag

Set the database to emergency mode so that it becomes read only and not accessible to others

Check the integrity among all the objects

Set the database to single user mode

Repair the errors

Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

Here’s the same code for you to try out

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

Great article. I am fully impressed.LG Networks, Inc. is a Microsoft Certified and a leading Microsoft Exchange Server Consulting firm.Our Emergency Exchange Support consultants can help you attain the maximum benefit and stability fromMicrosoft Exchange Server with administrative, technical and onsite or remote troubleshooting support.Waoou... What a tutorial you made? I think any body will be clear after seen this. Just not only looking nice , alsomore helpful. Emergency Exchange Support