SQL Server suspect Database solution

1. First confirm that the. mdf and. ldf files have been backed up.

2. Create a new database in SQL Server with the same name, and then stop the SQL Server service.

3. Overwrite the. mdf and. ldf files for the new database with the original. mdf and. ldf files.

4. Restart the SQL Server service, which should see this database in a suspect (suspect) state.

5. Execute the following command in SQL Query Analyzer to allow the system tables to be updated:

Use Mastergosp_configure ' allow updates ', 1reconfigure with Overridego

6. Put this database in emergency mode:

Update sysdatabases Set status = 32768 where name = ' db_name ' Go

7. Use the DBCC CHECKDB command to check for errors in the database:

DBCC CHECKDB (' db_name ') go

8. If the DBCC CHECKDB command fails, go to step 10th, or set the database to Single-user mode before attempting to fix it:

sp_dboption ' db_name ', ' Single user ', ' true ' DBCC CHECKDB (' db_name ', repair_allow_data_loss) go

If you are prompted to say that the database is not in Single-user mode while executing the DBCC CHECKDB (' db_name ', repair_allow_data_loss) command, restart the SQL Server service and continue with the attempt.

9. If the DBCC CHECKDB (' db_name ', repair_allow_data_loss) command fails, go to step 10th, or if the error in the database is successfully repaired:

Re-execute the DBCC CHECKDB (' db_name ') command to confirm that no errors exist in the database.

Clear the suspect status of the database: Sp_resetstatus ' db_name '

Clear the Single-user mode status of the database: sp_dboption ' db_name ', ' single ', ' false '

Restart the SQL Server service, and if everything works, the database has been successfully restored.

10. If none of the above steps solve the problem, please refer to the documentation in the attachment to try to recover the data in the database by rebuilding the transaction log. If you have only MDF files, the problem is more complex and we need to rebuild the transaction log directly:

1. Create a new database in SQL Server with the same name, and then stop the SQL Server service.

2. Overwrite the. mdf file of the new database with the original LDF file, and delete its log file (. ldf).

3. Start the SQL Server service and put the database in emergency mode (IBID.: Steps 5 and 6).

4. Stop and restart the SQL Server service.

5. Execute the following command to rebuild the database log file: (Here is an example, you will use your actual database name)

DBCC rebuild_log (' cas_db ', ' d:cas_dbcas_db_log.ldf ')

6. Reset the database to Single-user mode.

7. Try again to check and repair errors in the database using the DBCC CHECKTABLE or DBCC CHECKDB command.