How to recover a database that is in suspect stage?
Parag Patel
Entrepreneur | Student of Life Time | Database Engineer | MS SQL DBA AZURE Mentor | Career Coach | Investor | Nutritional Therapist | Risk Takers | Problem Solver | Consultant
ALTER DATABASE test_db SET EMERGENCY
After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects, but the database will be in read-only mode. Any attempt to modify data will result in an error similar to the following:
Msg 3908, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database ‘test’ …..etc
ALTER DATABASE test SET SINGLE_USER
GO
DBCC CHECKDB (‘test’, REPAIR_ALLOW_DATA_LOSS) GO
If DBCC CHECKDB statement above succeeds the database is brought back online (but you’ll have to place it in multi-user mode before your users can connect to it). Before you turn the database over to your users you should run other statements to ensure its transactional consistency. If DBCC CHECKDB fails then there is no way to repair the database – you must restore it from a backup.