Monday, March 19, 2012

How to recover SQL Server database from suspect status

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.


Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.

Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

Did you first run a checkdb? What were the errors from the checkdb?

What errors are in the SQL Server error log? Do you have backups available to restore from? That's a better option if the checkdb reported allow_data_loss was the repair level required.

-Sue

|||

Thanks for the response. The problem is resolved. We brought the database in emergency mode and then in single user mode. Later we were able to recover the database using the checkdb utility, but with some loss of data.

No comments:

Post a Comment