SQL Database recovery file lost error Delete wrong format questionable error repair database suspect fix summary/sql SERVER 2000/2005/2008/2008r2

There are a number of reasons why a database can be questioned, and the steps used for different problems will vary, and the following steps do not apply to all cases, but include some basic steps.

Database doubt is that the database is in an inconsistent state, there is likely to be data loss. We recommend that you check for DBCC CHECKDB without errors before you make a database backup, and that the backed up database is not changed.

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 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. Place 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 when you execute the DBCC CHECKDB (' db_name ', repair_allow_data_loss) command that the database is not in single-user mode, restart the SQL Server service, and then continue to try.

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

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

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

Clears the single-user mode state of the database: sp_dboption ' db_name ', ' one user ', ' 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, refer to the documentation in the attachment to try to recover 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. Overwrite the. mdf file for the new database with the original LDF file and delete its log file (. ldf).

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

4. Stop and restart the SQL Server service.

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

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

6. Re-place the database in single-user mode.

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

SQL Database recovery file lost error Delete wrong format questionable error repair database suspect fix summary/sql SERVER 2000/2005/2008/2008r2