How to Recover or Repair Suspect Database in SQL Server

There are many reasons that can cause a database to go in suspect mode. Below is the list of such reasons for a suspect database. Here i will show you how to recover or repair suspect database in SQL Server.

  • Improper shutdown of the database or database server.
  • Hardware Failure.
  • Corruption in database file/s.
  • There is not enough space available for the SQL Server to recover the database during startup.
  • Inaccessible database files or Insufficient memory.
  • Database files drive letters mistyped/changed during any maintenance work.

To troubleshoot this recover or repair suspect database,  first you need to analyze your database server. You need to check whether all database files are in same location where they should be. if not, get the reason why files have been moved to another drive and by whom. You can move the files to previous location or you can change/update the location of these database files in SQL Server catalogue view using ALTER command.

Related Articles:

Sometimes, database files drive letters got changed as human error during some maintenance work. If this is the case, don’t do anything on the database server and ask your windows team to revert the drive letter name where your database files have been placed to the correct name. You can also do this change if you are authorized to do this.

Once you have checked the drive letters and there is no change in it then you can try to run below SQL statement to bring your database online.

--Change DBNAME with your database name that is showing in suspect mode.
Alter database DBNAME SET ONLINE.

Sometimes databases come online by running above Alter statement if there is no serious issue with the database. If nothing works then we will run below t-sql code to get all errors due to which database went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once you have enough information about errors then your next step would be Repair. If error is showing data files missing then check your data file drive connectivity from storage side if it’s missing then you don’t need to do anything just contact someone from storage or one who manages storage in your environment and ask them to check data file drive is healthy or not. If storage is also ok then we will repair our database using below t-sql codes:

ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;

Put your db name in place of [DBNAME] in above script. Above alter statement will change database into emergency mode and then repair the corruption present on data pages.

If your database does not come online post running above Alter statement then only option left is to restore your database with the copy of latest backups.

Note: REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

I hope you like this article. Please drop your questions and feedbacks in below comment section. You can also follow our Facebook page and Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *