Blog

Query to Repair Suspect Database In SQL Server

Posted by Kunal Vaishya Blogs | SQL Oct 26, 2012
This is the query to repair suspect database In SQL Server. If your Database is marked as suspected, here are the steps to fix it.

This is the query to repair suspect database in SQL Server.  

If your Database is marked as suspected, here are the steps to fix it.

In this you have replace “dbName” with suspected db name and run this query in master database.

Step 1 

--command to recover suspected database

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

This step 1 cannot set Index created for that database so now we have to rebuild the index. This query should be run in that suspected database.

Step 2

-- command to rebuild all indexes

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
COMMENT USING
PREMIUM SPONSORS
Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.
Employers - Post Free Jobs
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.