HOW TO FIX SQLSERVER DATABASE SUSPECT?
如何修復(fù)數(shù)據(jù)庫狀態(tài)"置疑"?

**********************************************************

Author:黃山光明頂

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需轉(zhuǎn)載,請(qǐng)注明出處!,如果有問題請(qǐng)發(fā)MAIL給我:-))

***********************************************************
在MS SQLSERVER中一直有這樣的問題,SQLSERVER的狀態(tài)"置疑",我們先來分析一下SQLSERVER數(shù)據(jù)庫"置疑"的原因:
   1.錯(cuò)誤的刪除日志;
   2.硬件(HD)損壞,造成日志和數(shù)據(jù)文件寫錯(cuò)誤;
   3.硬盤的空間不夠,比如日志文件過大;

解決辦法:

這是最簡單的辦法是有數(shù)據(jù)庫的全備份,然后恢復(fù)即可.
步驟:

1. 刪除原始的數(shù)據(jù)庫:
     USE MASTER
     GO 
     DROP DATABASE DB_SUEPECT
    

2.建立同名的數(shù)據(jù)庫:
        USE master
       GO
       CREATE DATABASE DB_SUSPECT
        ON
         ( NAME = DBNAME_DAT,
           FILENAME = ‘C:‘,
           SIZE = 10,
            FILEGROWTH = 5 )
           LOG ON
          ( NAME = ‘DBNAME_LOG‘,
           FILENAME = ‘g:‘,
           SIZE = 5MB,
           FILEGROWTH = 5MB )
           GO
    

3.恢復(fù)數(shù)據(jù)庫:
      RESTORE DATABASE DB_SUSPECT
     FROM DBNAME_BACKUP.DAT
  

4.數(shù)據(jù)庫完整性檢測:
       DBCC CHECKDB(‘DB_SUSPECT‘)

5.重新啟動(dòng)MSSQLSERVER服務(wù).

如果沒有全備份,那就要用一些特殊的方法:

1.設(shè)置數(shù)據(jù)庫為緊急模式
       Use Master
       GO
       sp_configure ‘a(chǎn)llow updates‘, 1
       reconfigure with override
      GO
      UPDATE sysdatabases SET status = 32768 where name = ‘DB_SUSPECT‘
      GO

2.停掉SQL Server服務(wù):
     NET STOP MSSQLSERVER

3.把原始數(shù)據(jù)庫的數(shù)據(jù)文件DBNAME_DAT.MDF,DBNAME_LOG.LDF移走:

4.啟動(dòng)SQL Server服務(wù):
      NET START MSSQLSERVER

5.重新建立一個(gè)同名的數(shù)據(jù)庫DB_SUSPECT；

       USE master
       GO
       CREATE DATABASE DB_SUSPECT
        ON
         ( NAME = DBNAME_DAT,
           FILENAME = ‘C:‘,
           SIZE = 10,
            FILEGROWTH = 5 )
           LOG ON
          ( NAME = ‘DBNAME_LOG‘,
           FILENAME = ‘g:‘,
           SIZE = 5MB,
           FILEGROWTH = 5MB )
           GO

6.設(shè)置數(shù)據(jù)庫運(yùn)行在單用戶的模式:
         USE MASTER
        GO
        ALTER DATABASE DB_SUSPECT SET SINGLE_USER
        GO

7.停掉SQL服務(wù):
     NET STOP MSSQLSERVER

8.把原來的數(shù)據(jù)文件再覆蓋回來:

9.啟動(dòng)SQL Server服務(wù):
      NET START MSSQLSERVER

10.重新設(shè)置SQLSERVER的狀態(tài):
         USE MASTER
        GO
        EXEC sp_resetstatus "DB_SUSPECT"

11.數(shù)據(jù)庫完整性檢測:
        DBCC CHECKDB(‘DB_SUSPECT‘)

12.恢復(fù)數(shù)據(jù)庫為多用戶模式:
        USE MASTER
        GO
        ALTER DATABASE DB_SUSPECT SET MULTI_USER
       GO

13.恢復(fù)SQLSERVER原始的配置:
      USE MATER

    GO     

    UPDATE sysdatabases SET status = 4194320 where name = ‘DB_SUSPECT‘
    GO

14.配置SQLSERVER不允許更新系統(tǒng)表:
      USE MASTER
     GO
      sp_configure ‘a(chǎn)llow updates‘, 0
      reconfigure with override
     GO

15.重新啟動(dòng)MSSQLSERVER服務(wù):

     最好重新啟動(dòng)操作系統(tǒng)

16.備份數(shù)據(jù)庫:

   可以通過SQLSERVER企業(yè)管理器或T-SQL.需要備份MASTER和DB_SUSPECT