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.
- How to get Last Restore Time of your database?
- Understanding Data Files, Pages & Extents
- Understanding SQL Server Recovery Models
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.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018