How to fix a database state

Data | database
How to FIX SQL Server DATABASE suspect?
How do I fix the database state "suspect"?

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

Author: Huangshan Bright Top

Mail:leimin@jxfw.com

version:1.0.0

Date:2004-1-30

(If you want to reprint, please specify the source!), if you have any questions, please send mail to me:-)

***********************************************************
In the case of MS SQL Server, where the state of SQL Server is "questionable", let us first analyze the reason for the SQL Server database "suspect":
1. Incorrect deletion of log;
2. Hardware (HD) damage, resulting in log and data file write errors;
3. Hard disk space is not enough, such as log file is too large;


Solution:

The easiest way to do this is to have a full backup of the database and then restore it.
Steps:

1. Delete the original database:
Use MASTER
Go
DROP DATABASE Db_suepect


2. Establishment of a database with the same name:
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. Restore the database:
RESTORE DATABASE Db_suspect
From Dbname_backup. Dat


4. Database Integrity Detection:
DBCC CHECKDB (' Db_suspect ')

5. Restart the MSSQLServer service.

If you don't have full backups, you'll need to use some special methods:

1. Set Database as emergency mode
Use Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go
UPDATE sysdatabases SET status = 32768 where name = ' Db_suspect '
Go

2. Deactivate SQL Server service:
NET STOP MSSQLServer

3. Dbname_dat the data file of the original database. Mdf,dbname_log. LDF removal:

4. Start SQL Server service:
NET START MSSQLServer

5. Re-establishing a database db_suspect with the same name;

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. Set the database to run in Single-user mode:
Use MASTER
Go
ALTER DATABASE db_suspect SET single_user
Go

7. Stop SQL Service:
NET STOP MSSQLServer

8. Overwrite the original data file back:


9. Start SQL Server service:
NET START MSSQLServer

10. Reset the state of SQL Server:
Use MASTER
Go
EXEC sp_resetstatus "Db_suspect"

11. Database Integrity Detection:
DBCC CHECKDB (' Db_suspect ')

12. Restore the database to multiuser mode:
Use MASTER
Go
ALTER DATABASE db_suspect SET multi_user
Go

13. Restore SQL Server's original configuration:
Use Mater

Go

UPDATE sysdatabases SET status = 4194320 where name = ' Db_suspect '
Go

14. Configuring SQL Server does not allow system tables to be updated:
Use MASTER
Go
sp_configure ' allow updates ', 0
Reconfigure with override
Go

15. Restart the MSSQLServer service:

It is best to restart the operating system

16. Back up the database:

Can be done through SQL Server Enterprise Manager or T-SQL. Need to back up master and Db_suspect