This article will explain to you how to solve SQL Server doubtful databases. Sometimes a database is marked as doubtful by accident. Here are some solutions. 1. First confirm that the .mdf and .ldf files have been backed up.
2. Create a new database with the same name in SQL Server, and then stop the SQL Server service.
3. Overwrite the .mdf and .ldf files corresponding to the new database with the original .mdf and .ldf files.
4. Restart the SQL Server service. You should see that the database is in suspect status.
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 into 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 10, otherwise put the database into single-user mode before trying to repair it:
sp_dboption 'db_name','single user','true'DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS)GO
If you are prompted that the database is not in single-user mode when executing the DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS) command, restart the SQL Server service and continue trying.
9. If the DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS) command fails, go to step 10, otherwise if the error in the database is successfully repaired:
Re-execute the DBCC CHECKDB('db_name') command to confirm that there are no errors 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 user','false'
Restart the SQL Server service. If everything is normal, the database has been successfully restored.
10. If the above steps cannot solve the problem, please refer to the attached document and try to restore the data in the database by rebuilding the transaction log. If you only have MDF files, the problem is more complicated and we need to rebuild the transaction log directly:
1. Create a new database with the same name in SQL Server, and then stop the SQL Server service.
2. Use the original ldf file to overwrite the .mdf file corresponding to the newly created database, and delete its log file (.ldf).
3. Start the SQL Server service and put the database into emergency mode (same as above: Step 5 and Step 6).
4. Stop and restart the SQL Server service.
5. Execute the following command to rebuild the database log file: (The following is an example, you need to use your actual database name)
DBCC REBUILD_LOG('cas_db', 'D:cas_dbcas_db_Log.LDF')
6. Replace the database into single-user mode.
7. Try again using the DBCC CHECKTABLE or DBCC CHECKDB command to check and fix errors in the database
-