SQL – How To Restore A Suspect Database SQL Server 2005

Occasionally an SQL database can lock-down in suspect mode. There are many causes, but the main is an expected shot-down or power loss. I had a slightly loose fitting CPU fan (and I mean ightly, one of the pins was a little less secure than the rest) in a server running SQL server 2005. It took a morning of stripping back the chassis to find it. Since re-seating the fan pins all is well.

To cut a long story short, the following script allowed me to restore the suspect database in its entirety. SQL server 2005 comes with the DB status of Emergency. This can be used to alter the status from Suspect mode (in which the database is inaccessible), to Emergency mode where you can gain access to the database and retrieve data from the tables along with those all important stored procedures and other scripts.

Once in Emergency mode you can re-run DBCC checkDB to check the integrity of all the pages and structures that make up the tables.
This script uses the REPAIR_ALLOW_DATA_LOSS argument which tries to repair all reported errors. These repairs can cause some data loss. Hopefully you have a recent back up, if not take backups from the database while you can access it in emergency mode!
REPAIR_ALLOW_DATA_LOSS is used to ensure the database is returned to a structurally and transitionally consistent state

This cannot be undone.

Here is the script:

EXEC sp_resetstatus DATABASENAME;
ALTER DATABASE DATABASENAME SET EMERGENCY
DBCC checkdb(DATABASENAME)
ALTER DATABASE DATABASENAMEĀ SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASENAME SET MULTI_USER

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s