SQL Server database remediation for suspect, suspect, offline, single user, emergency mode, etc.

Database suspect, suspicious, offline, single-user, emergency mode is mainly because the database of log files in addition to the problem, 2000 and 2008 repair method is not the same, 2008 of the repair script in 2000 does not apply, mainly is not recognized by 2000.

Assume that the database name is: Eisdoc:

How the sqlserver2000 database is handled in doubt:

1. 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.

2. Set Eisdoc to Emergency Repair mode

Update sysdatabases set status=-32768 where dbid=db_id (' Eisdoc ') is turned off to open Enterprise Manager, which you can see in SQL Server Enterprise managers that the database is in Read-only \ suspect \ offline \ Emergency mode can see the tables in the database, but only the system tables

3. Rebuilding a database log file

DBCC REBUILD_LOG (' Eisdoc ', ' E:\Microsoft SQL server\data\eisdoc_log.ldf ') executes during execution if the following prompt is encountered: server: Msg 5030, Level 16, State 1, line 1 failed 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 the database and if you have just opened the system table for the Eisdoc library using SQL Server Enterprise Manager in the F step, exit SQL Server Enterprise Manager. (Shutting down Enterprise Manager, if other machines are accessing the database from the network and shutting down the network), the prompt for completion should resemble the following: Warning: The Log for database ' Eisdoc ' 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.

4. Verifying database consistency (can be omitted)

DBCC CHECKDB (' ibusinesswork ') general execution results are as follows: Checkdb found 0 allocation errors and 0 conformance errors (in database ' Eisdoc '). DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

5. Setting the database to a normal state

sp_dboption ' Eisdoc ', ' dbo using only ', ' false ' if there is no error, then the restored database can now be used normally.

6. Turn off the Allow direct modification to system catalog item.

Because the direct operating system table is a relatively dangerous thing. Of course, we can recover from SQL Server Enterprise Manager.

How to fix suspicious files in sqlserver2008 database:

1. Set the database status , script: EXEC sp_resetstatus ' Eisdoc '

2. Set the database to emergency mode , script: ALTER db Eisdoc set EMERGENCY

3. Set the database to single-user mode (check for database repair only in single-user mode), script: ALTER database Eisdoc set Single_user

4. Check and repair the database , you will be prompted to repair the database may lose data, normal should not lose the database data, but the integrity of the database log will be destroyed, script: DBCC CheckDB (Eisdoc, Repair_allow_data_ LOSS)

5. Restore the database to multi-user mode , script: ALTER db eisdoc SET Multi_user

-- 恢复数据库状态 ALTER DATABASE dbName  SET ONLINE

Five steps to repair suspicious files in sqlserver2008

Original link: http://blog.sina.com.cn/s/blog_61feb2110101e2ez.html

SQL Server database remediation for suspect, suspect, offline, single user, emergency mode, etc.