Database Suspect Solution

Method One: Database recovery log file error handling database: MSSQL SERVER   Enterprise Edition problem description: Database suspect. The database backup file is corrupt. The database physical file (*. MDF), using the database attach function, attach failed. Hint Error: Server: Msg 1813, Level 16, State 2, line 1 failed to open new database ' test '. The CREATE DATABASE terminates. Device activation error. Physical file name ' D:\test_log. LDF ' may be wrong. Find related data solutions as follows: A. We use the default method to establish a database (such as test) to be used for recovery. can be established within SQL Server Enterprise Manager. B Shut down the database server. C Test_log.ldf Delete the log file for the database you just generated, overwriting the database data file test_data.mdf that you just generated with the database MDF file you want to recover. D Start the database server. You will see that the status of the database test is "suspect". No action can be made on this database at this time. E. Set the database to allow direct operating system tables. You can select the database server in SQL Server Enterprise Manager, right-click, select Properties, and in the Server Settings page, select the Allow direct modification to system directory. You can also use the following statement to implement. Use Mastergosp_configure "Allow updates", 1go reconfigure with Overridegof. Set test for Emergency Repair mode update sysdatabases set status=-32768 where dbid=db_id (' test ') is now available in SQL Server Enterprise The manager sees the database in "read only \ suspect \ offline \ Emergency mode" to see the table inside the database, but only the system table G. The following performs a true recovery operation, rebuilding the database log file DBCC REBUILD_LOG (' Test ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf ') during execution , if you encounter the following prompt: Server: Msg 5030, Level 16, State 1, line 1 failed to lock the database to perform the operation. DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. Indicates that your other program is using theDatabase, if you have just used SQL Server Enterprise Manager in the F step to open the system table for the test library, exit SQL Server Enterprise Manager. The correct execution of the completion prompt should resemble the following: Warning: The Log for database ' test ' 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 (' test ') general execution results are as follows: Checkdb found 0 allocation errors and 0 conformance errors (in database ' test '). DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. I. Set the database to the normal state sp_dboption ' test ', ' dbo using only ', ' false ' if there is no error, then congratulations, you can now use the restored database properly. 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 complete sp_configure ' allow updates ' using the following statement, 0go reconfigure with overridego  Two: Solution for SQL Server attach database error 823 2008-10-13 15:06sqlserver Additional database error 823 solution One, Sql-server attach the database fails. 1, abnormal situation: the server in the normal operation of the sudden power outage, resulting in database file corruption, the specific performance is: The database name is followed by "(suspect)" word. 2. Exception Analysis: About 823 error in Sql-server Help: ================================ Error 823 severity level 24 The offset of the message body in the file "%4!"%3! %2! of the Place An I/O error was detected in the process,%1! Explain that Microsoft SQL Server is making a read or write request to a deviceI/O error encountered. This error usually indicates a disk problem. However, other core messages that are logged in the error log before error 823 should indicate which device is involved. 3. Workaround: In Sql-server Enterprise Manager, after creating a new database with the same name (this is assumed to be test), stop the database and overwrite the corrupted database file Data.mdf with the data.mdf in the new database directory, and delete the Test_log. LDF file (Note: The log file is an empty log file that is generated when a new database is created); Start the database service and find the word "suspect" behind the database name test. It doesn't matter, open the SQL self-brought Query Analyzer and execute the following SQL statements: First, exec sp_configure ' allow updates ', 1 RECONFIGURE with OVERRIDE/* Open the switch to modify the system table */second, update sysdatabases set status=32768 where name= ' database name '/* Sets the database state */third, DBCC rebuild_log (' Database name ', ' d:/database/test_log.ldf ')/* heavy Build LDF file */IV, UPDATE sysdatabases set status=0 where name= ' database name '/* * Reset Database status */fifth, restore database name with RECOVERY/* Recover data Library */VI, EXEC sp_configure ' allow updates ', 0-RECONFIGURE with OVERRIDE//close Open the switch to modify system tables */Follow this method, you should be able to repair the database normal access. If the problem persists, the stupidest way to do this is to create another database and export the data from each table in the original database (Test) to the new database table. ============================================================ added: Use the above six steps to solve the problem of database doubt, but the database table also has a corrupt tables (Inf_gdscode) , and the bad table is not successful when it is exported. Finally run in Query Analyzer: Use Nmgbt_hcxuexipos (database name) godbcc checktable (' Inf_gdscode ', Repair_allow_data_loss) GO

Database suspect workaround