Attempt to Recover a Suspect Database

By SQLRx Admin | SQL Administration

Mar 23
Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation!  Refer to the script below for help.

SQL Server Administration:  You can try to save data by creating an empty database and copying tables to it after placing the database into EMERGENCY and then SINGLE_USER state. Most of the time, a suspect database has to be restored to be able to be used again. However, sometimes it is possible to repair a suspect database. I recently did just that for a client, and recovered 4 suspect databases.

Use the script below to check the integrity of the database or to repair it. Running an integrity check will tell you where the errors are and what the minimum repair you can do is. Always make sure you have database and transaction log backups so you can recover a failed database to point in time, if necessary. 

— Attempt repair suspect database

USE master;

GO

— Set database in EMERGENCY read only state

ALTER DATABASE MyDB SET EMERGENCY

GO

— Set database in SINGLE_USER state

ALTER DATABASE MyDB SET SINGLE_USER

GO

USE MyDB

DBCC CHECKDB(MyDB) WITH NO_INFOMSGS  — Run integrity Check

— DBCC CHECKDB (MyDB, REPAIR_REBUILD) WITH NO_INFOMSGS  — Repair database with no dataloss

— DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS  — Repair database with possible dataloss

GO

— Set database in MULTI_USER state

ALTER DATABASE MyDB SET MULTI_USER

GO

 

Share
Tweet
Share
Share

About the Author

Previous Post Find out How Much Data Files are Waiting with Filestats Next Post What’s Causing Files to Automatically Grow
>