MS SQL Server database or table repair

When you are prompted that the database is in recovery mode, rebuild the log file to resolve DBCC rebuild_log (' db_name ', ' C:\mssql7\data\dbxxx_3.LDF ')

MS SQL Server provides a number of database repair commands that can be tried when the database is questioned or if a read cannot be completed.

1. DBCC CHECKDB
After restarting the server, execute the following SQL in SQL Query Analyzer to repair the database without any action, and fix the consistency errors and allocation errors that exist in the database.

Use master
DECLARE @databasename varchar (255)
Set @databasename = ' Name of the database entity to be repaired '
exec sp_dboption @databasename, n ' single ', n ' true '--to place the target data base in single-user state
DBCC CHECKDB (@databasename, Repair_allow_data_loss)
DBCC CHECKDB (@databasename, Repair_rebuild)
exec sp_dboption @databasename, n ' single ', n ' false '--to set the target data base to multi-user state

Then execute DBCC CHECKDB (' name of the database entity that needs to be repaired ') to check whether the database still has errors. Note: Repair may result in loss of some data.

2. DBCC checktable
If the DBCC CHECKDB check still has errors, you can use DBCC CHECKTABLE to fix them.
Use the name of the database entity that needs to be repaired
DECLARE @dbname varchar (255)
Set @dbname = ' Name of the database entity to be repaired '
exec sp_dboption @dbname, ' Single user ', ' true '
DBCC CHECKTABLE (' name of the data table to be repaired ', repair_allow_data_loss)
DBCC CHECKTABLE (' name of the data table to be repaired ', repair_rebuild)
------Change the name of the data table that needs to be repaired to the name of the data table in which the DBCC CHECKDB times error was performed
exec sp_dboption @dbname, ' Single user ', ' false '

3. Other Common repair commands
DBCC Dbreindex Rebuilding one or more indexes of a table in a specified database
Usage: DBCC dbreindex (table name, ') fixes all indexes on this table.

===================================

Methods of detecting and repairing SQL Server database

1.1 Detection of SQL Server databases
SQL Server provides commands for database detection, which can be used by DBCC CHECKDB to detect the allocation and structure of individual objects in a database, and to display all error messages through a single parameter control. Its syntax is as follows:
DBCC CHECKDB
(' database_name ' [, NOINDEX | {Repair_allow_data_loss
| Repair_fast
| Repair_rebuild
}]
) [With {ALL_ERRORMSGS | NO_INFOMSGS}]
Parameter description:
' database_name ' represents the database entity name being detected;
Noindex means non-clustered index of non-system table is not detected;
Repair_allow_data_loss | Repair_fast| Repair_rebuild refers to a direct fix found error, where Repair_allow_data_loss represents, if this error cannot be repaired, the system will directly delete the relevant data. With any of these three parameters, the database must be in single-user mode and can be set in the database properties in Enterprise Manager;
The ALL_ERRORMSGS representative will display all detected error messages, otherwise, only 200 error messages are displayed for each table;
NO_INFOMSGS represents a report that hides all information and occupies space.
After detection, for the wrong object, the information of the specific error will be reported as Object ID, and the related table, or name, can be found in the system table sysobjects based on the object ID.

1.2 Fix for SQL Server problem database
After the database detection, we can take the corresponding measures to deal with the problems that arise. If there is a problem with the physical storage of the object after detection, the DBCC CHECKALLOC can be used to fix it:
DBCC checkalloc (' database_name ' | Repair_rebuild}]) [With {ALL_ERRORMSGS | NO_INFOMSGS}]
If there is an error indexing non-system objects, the DBCC DBREINDEX can be used to fix them:
DBCC dbreindex ([' Database.owner.table_name ' [, index_name [, FillFactor]]) [with NO_INFOMSGS]
In both cases, you can also use the DBCC CHECKDB (' db_name ', repair_rebuild) to fix it directly.
Another scenario is that the data connection cannot be established when the test is in progress, indicating that the database is corrupted. In this case, we can take the following measures to try to repair.
First, create a new database in SQL Enterprise (such as the database named Test), stop the SQL Server Service Manager after the database is built, and rename the MDF file for the customer database to test _data.mdf ( That is, the primary file name of the new database), and then overwrite the file with the name of the newly created database with the renamed file, and then start SQL Server Service Manager. Set the system table to a change state for the master database
Use Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go
Set the database to a state of emergency:
Update sysdatabases Set status = 32768 where database '
Stop and restart SQL Server Service Manager and rebuild the log file:
DBCC TRACEON (3604)
DBCC rebuild_log (' Test ', ' Test _log_ldf ')
Set the database to single-user mode and then detect:
sp_dboption ' Test ', ' Single user ', ' true '
DBCC CHECKDB (' test ')
Go
When this database executes CHECKDB, the indexes of some tables are found to be corrupted, and the indexes are rebuilt for the specific table:
DBCC dbreindex (table name)
If the above operation is still not resolved, if the index broken table is a temporary table or not a critical table, you can be introduced from the new establish accounts, if the main table, you may be a recent backup to (partial) recovery. If you do not have a backup, you cannot repair it.

1.3 Why is SQL Server database vulnerable to corruption?
Here are some of the reasons Microsoft may cause database corruption and some precautions:
Operation problems, including cold starter, hot-drawn hard disk, delete some database files;
Hardware issues, including disk controller issues;
Operating system issues, including some fatal errors related to the system.

1.4 Precautionary measures:
1, regular/irregular execution of Chkdsk (without parameters) to detect the physical structure of the hard disk and repair some chkdsk reported problems;
2, standing data.

1.5 Application Database Repair Example
DECLARE @databasename varchar (255)
Set @databasename = ' AIS20021224170730 '------Be sure to enter it manually
A fix that allows data loss to occur when there are problems---------performing a general fix
---------Data loss repair requires a single user, exit the middle tier, client, and other SQL modules
---All functions exit, set the database to single user in Query Analyzer master

exec sp_dboption @databasename, n ' single ', n ' true '

-----in Query Analyzer master, repair the database
DBCC CHECKDB (@databasename, Repair_allow_data_loss)
DBCC CHECKDB (@databasename, Repair_rebuild)
------Restoring Database state
exec sp_dboption @databasename, n ' single ', n ' false '


2nd. Repair of database log corruption
Follow the steps below to try to rebuild the database transaction log.

Note: Because the transaction log is missing, the database may have no committed data.

Note: You have to replace the real database name

2.1 Step 1:

Create a new database named the name of the original database.

2.2 Step 2:

Stop SQL Server

2.3 Step 3:

Replace the MDF file of the old database with the appropriate MDF file for the new database and remove the LDF file

2.4 Step 4:

Restart the SQL Server service, and then run the following command:

Use Master

Go

sp_configure ' allow updates ', 1

Reconfigure with override

Go

BEGIN Tran

Update sysdatabases Set status = 32768 where Db_name '

--Verify One row is updated before committing

Commit Tran

2.5 Step 5:

Stop SQL and then restart the SQL Server service, and then run the following command:

DBCC TRACEON (3604)

DBCC rebuild_log (' db_name ', ' C:\mssql7\data\dbxxx_3.LDF ')

Go

2.6 Step 6:

Stop SQL and then restart the SQL Server service, and then run:

Use master

Update sysdatabases SET status = 8 where
Go

sp_configure ' allow updates ', 0

Reconfigure with override

Go

2.7 Step 7:
Run DBCC CHECKDB (DB_NAME) to check the integrity of the database.

3rd General processing of database queries
1. Execute the following SQL (open the switch to modify the system table):
EXEC sp_configure ' allow updates ', 1
RECONFIGURE with OVERRIDE
2. Modify the table in database master: sysdatabases
Change the Status field value to 4
3. Then execute the following sql:
EXEC sp_configure ' allow updates ', 0
RECONFIGURE with OVERRIDE.

MS SQL Server database or table repair