SQL SERVER 2000 database suspect processing

    because the server unexpectedly loses power, causes the database on the SQL Server server to appear "suspect" but cannot use, through the net search, found the following method solves the problem, here records: when produces the database to suspect, Both the database file and the log file exist, and if the database file does not exist, it is processed separately. 1, stop the database server, the database MDF file and the LDF file copy backup one copy 2, start the database server, delete the suspect database 3, only the database MDF file attached to the backup database, sp_attach_db or Sp_attach_single_file_ DB can attach a database with a message similar to the following: Device activation error. The physical file name ' C:\Program Files\Microsoft SQL Server\mssql\data\mydb_log.ldf ' may be incorrect. A SQL server\mssql\data\mydb_log named ' C:\Program Files\Microsoft has been created. LDF ' new log file. This indicates that the database attached to the success, the problem solved, if successful, congratulations, anyway, I am multibyte unsuccessful, prompted similar to the following error message failed to open the new database ' MyDb '. The CREATE DATABASE terminates. Device activation error. Physical file name ' E:\www\myDb_log. LDF ' may be wrong. At this point I used the following methods to solve (refer to the method on the Internet).    A. Our SQL Server Enterprise Manager creates a new database with the same name for recovery (note: MyDB in this case is the same name as the problem database).    B. Shut down the database server.    C. Delete the log file for the database you just generated mydb_log.ldf (in this example, the name of the column database, actually using your own database names), overwriting the newly generated database data file Mydb_data.mdf with the database MDF file that you just backed up.    D. Start the database server. You will see that the status of database MyDB is "suspect". No action can be made on this database at this time.    E. Set the database to allow direct operating system tables. This operation can select the database server in SQL Server Enterprise Manager, press the right--key, select "Properties", and on the "Server Settings" page, select "Allow direct modification to system directory". You can also use the following statement to implement.

use master go sp_configure ' Allowupdates',1 go reconfigure With override go

F. Set MyDB to Emergency Repair mode set the following command in the query manager:

Update sysdatabases set status=-32768 where dbid=db_id(' MyDb')

In SQL Server Enterprise Manager, you can see that the database is in read \ suspect \ offline \ Emergency mode and you can see the tables in the database, but only the system table G. Perform a real recovery operation below to rebuild the database log file

DBCC rebuild_log ('myDb','C:\Program Files\Microsoft SQL Server\mssql\data\mydb_ Log.ldf')

Warning: The Log for database ' MyDb ' has been rebuilt. The consistency of the transaction has been lost. You should run DBCC CHECKDB to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files. DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. When you open the SQL Server Enterprise Manager, you see that the status of the database is "only for dbo use."    You can now access the user tables in the database. H. Verifying database consistency (can be omitted)

DBCC checkdb ('myDb')

The general execution results are as follows: CHECKDB found 0 allocation errors and 0 conformance errors (in database ' MyDb '). DBCC execution is complete.    If DBCC outputs an error message, contact your system administrator. I. Setting the database to a normal state

sp_dboption ' myDb','dbo use only','false'

J In the final step, we will restore the "allow direct modification to the system directory" setting in step e. Because the direct operating system table is a relatively dangerous thing. Of course, we can recover from SQL Server Enterprise Manager, or we can use the following statement to complete

sp_configure ' Allowupdates',0 go reconfigure with override go

To this database to suspect the problem is resolved.

SQL SERVER 2000 database suspect processing