Database suspect (suspicious state) Repair method __ Database

the first thing to emphasize is that it is best to disconnect all connections in this database, not to shut down the database without committing transactions, or it may cause the log file to be unable to redo.

there are several possible options:

1. General circumstances

ALTER DATABASE DatabaseName SET Emergency


ALTER DATABASE DatabaseName SET single_user


DBCC CheckDB (DatabaseName, Repair_allow_data_loss)


ALTER DATABASE DatabaseName SET multi_user

You can restore the database state by following the above statement

2, the quickest way

Detach This database, transfer the log file backup to another folder, attach all the data files under this database, do not log files, automatically recreate the log files after attaching, this method database can be restored in the shortest time, but the following recovery is best to repair the database to avoid Sequela

DBCC CheckDB (DatabaseName, Repair_allow_data_loss)

3, if the database is suspicious can not execute the above operation statement, can also follow the steps below to solve the problem

SQL Server 2005/2008/2008r2 repair steps

Method One

ALTER DATABASE <databasename>set Emergency

Go

ALTER DATABASE <databasename>set Single_user with rollback immediate

Go

Use master

Go

ALTER DATABASE <databasename>rebuild LOG on

(Name=<databasename>_log,filename= ' C:\sql\logs\<databasename>_log.) LDF ')

Go

SELECT * FROM sys.databases

Go

DBCC CHECKDB (' <DatabaseName> ', Repair_allow_data_loss)

Go

sp_dboption ' <DatabaseName> ', ' Single user ', ' false '

Method Two

ALTER DATABASE <DatabaseName> SET emergency

Go

ALTER DATABASE <DatabaseName> set Single_user with rollback immediate

Go

Use master

Go

ALTER DATABASE <DatabaseName> Rebuild Log on

(Name=<databasename>_log,filename= ' C:\sql\logs\<databasename>_log.) LDF ')

Go

Use <DatabaseName>

Go

DBCC CHECKDB

Go

--At this point checkdb have errors to do step D)

d The database should be in a emergency state, create a new database, and export the data to the new database in Bcp/bulk insert/dts/ssis mode.

Note that in this case the index, stored procedure, view, and so on, all need to be manually guided out.

4. If the database cannot be switched to Single-user mode or cannot be operated or shut down normally, it is necessary to detach the database or go offline or turn off the database service so that its MDF files and ndf files are available,

Recreate the database with the same name, and create each NDF file in the same order of NDF file creation times as the database to be restored, and then replace the existing library file with the data file to be recovered using the following statement

ALTER DATABASE databasename
MODIFY FILE (NAME =logicname,
FILENAME = ' h:\file\name.ndf ')

ALTER DATABASE DatabaseName
MODIFY FILE (NAME =Logicname,
FILENAME = ' H:\file\name_log.ldf ')

Restart the database service after replacing, and then follow the steps below

ALTER DATABASE <databasename>set Emergency

Go

ALTER DATABASE <databasename>set Single_user with rollback immediate

Go

Use master

Go

ALTER DATABASE <databasename>rebuild LOG on

(Name=<databasename>_log,filename= ' C:\sql\logs\<databasename>_log.) LDF ')

Go

SELECT * FROM sys.databases

Go

DBCC CHECKDB (' <DatabaseName> ', Repair_allow_data_loss)

Go

sp_dboption ' <DatabaseName> ', ' Single user ', ' false '