Database-Challenged Solutions

Solve | data | database | doubt

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
Add that if you use Domain\User, you should pay attention to it. MDF. Permissions for the directory in which the LDF resides.

Zach's efficacious scriptZach says he runs the following script every time he encounters this kind of database doubt:

======================================================--befo Re running any script, run the following to set the master database to allow updates use master Go sp_configure ' allow updates ', 1 go reconfigure With OVERRIDE go  --run The following script UPDATE master. sysdatabases SET status = status ^ 256 WHERE name = ' database_name '  --run the following SCR IPT EXEC sp_resetstatus database_name  --stop and start the MSDTC at this stage --after the proc Edure is created, immediately disable updates to the system tables: exec sp_configure ' allow updat Es ', 0 go reconfigure with OVERRIDE go ================== ===================