SQL Server question database solution

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 stop the SQL Server service.

3. Use the original. mdf and. ldf files to overwrite the. mdf and. ldf files corresponding to the new database.

4. Restart the SQL Server service. This indicates that the database is in a suspicious state.

5. Run the following command in the SQL query analyzer to update the system table:

Use mastergosp_configure 'Allow updates', 1 reconfigure with overridego

6. Set this database to emergency mode:

Update sysdatabases set status = 32768 where name = 'db _ name' go

7. Use the dbcc checkdb command to check database errors:

Dbcc checkdb ('db _ name') GO

8. If the dbcc checkdb command fails, go to step 1. Otherwise, set the database to single-user mode and try to fix 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 running the dbcc checkdb ('db _ name', REPAIR_ALLOW_DATA_LOSS) command, restart the SQL Server service and try again.

9. If the dbcc checkdb ('db _ name', REPAIR_ALLOW_DATA_LOSS) command fails, go to step 1. Otherwise, if the database error is fixed successfully:

Run the dbcc checkdb ('db _ name') command again to confirm that there are no errors in the database.

Clear the suspicious status of the database: sp_resetstatus 'db _ name'

The single-user mode status of the database is cleared: sp_dboption 'db _ name', 'single user', 'false'

Restart the SQL Server service. If everything is normal, the database is successfully restored.

10. If the above steps do not solve the problem, please refer to the document in the attachment to try to restore the data in the database by recreating the transaction log. If you only have MDF files, the problem will be more complicated. We need to re-create 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 corresponding to the new database with the original ldf file, and delete the log file (. ldf.

3. Start the SQL Server service and set the database to emergency mode (same as above: Steps 5 and 6 ).

4. Stop and restart the SQL Server service.

5. Execute the following command to recreate the Database Log File: (The following is an example. You must use your actual database name)

DBCC REBUILD_LOG ('cas _ db', 'd: cas_dbcas_db_Log.LDF ')

6. Reset the database to the single-user mode.

7. Try again using the dbcc checktable or dbcc checkdb command to check and fix database errors.