The usual type of corruption for a table is mentioned above in cross linked objects. If is not occurring and the table is still corrupt, there are a few things you can do.

Attempt to BCP all of the data out. If this works, you can drop and recreate the table. Once the table is recreated, BCP the data back in. If the data won't BCP out or the table won't drop go on to the next two suggestions.

Restore the most recent backup to another server and apply all transaction logs. Drop and recreate the corrupt table. Use Transfer Manager or BCP to move the data back into the production server. You have to make sure that the data is completely up to date and no other transactions are being processed in order to protect you from primary/foreign key violations. If the table won't drop, then go on to the next suggestion.

Perform a single table restore from the most recent backup. You must be careful of and account for any key relationships in the database. If a single table restore does not work due to data conflicts or the table contains a text or image column, continue on to the final suggestion.

The final way to recover is to restore the entire database and apply all transaction logs. This is also the safest way in order to ensure you account for any primary - foreign key relationships.

Corrupt System Table

There is exactly one way to recover a corrupt system table. That is to restore from the most recent backup.

Corrupt User Database

If a user database gets corrupted, you must apply the most recent backup. As an additional safety precaution you should drop the database using either the drop database command. The database is then recreated during the restore process. This ensures that you are not carrying around anything from the corrupted database.

Suspect database

A suspect database can occur for many reasons. In some cases SQL Server simply could not open the .mdf or .ldf files that contain the database and transaction log or it simply gets lost and marks it suspect. In a few cases, the database actually corrupts to such a level that SQL Server can no longer access it. You must fix this by the following procedure.

1. First you must investigate the NT event log and SQL Server error log to see if anything was reported that led to the database being marked suspect.
2. If errors can be found, execute an sp_resetstatus
3. Stop and start SQL Server
4. If the database comes up clean, open it back up for production.

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32