Monday, October 4, 2010

Repair & Restore SQL Server Database from Suspect Mode


Sometimes when you try to connect to your SQL database server, you may get your SQL database in suspect mode. At that point your database server would not allow you to access and perform any operation on it. Your database will accessible only when the database will be repaired. For fixing the problem you will have to use advanced SQL database recovery tool.

SQL server database can go in suspect mode for many reasons; some of them are given below:

Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted



You can know the exact reason for the SQL database suspect mode. You will have to use given below query for knowing the exact reason.

“DBCC CHECKDB (’YourDatabasename’) WITH NO_INFOMSGS, ALL_ERRORMSGS”

After running the above query you will able to know the exact reason of the database suspect mode.

To fix the suspect mode and restore the database, run following queries in Query Analyzer of SQL server database,

“EXEC sp_resetstatus ‘yourDatabasename’;
ALTER DATABASE yourDatabasename SET EMERGENCY
DBCC checkdb(’yourDatabasename’)
ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDatabasename SET MULTI_USER”

At finally you have done it. But sometimes after the running of above queries in query analyzer you are still unable to perform any operation on the database. This means your database has got badly corrupted. At that situation you will have to use third party SQL server recovery software to repair the database.


Stellar Phoenix SQL database recovery tool is the advanced repair and restore tool that support MS SQL server 2008, 2005, 2000 and 7.0. It can also repair the NDF files of the database. It is comfortable with windows 7, XP, Vista, 2003, 2000 and NT.

No comments:

Post a Comment