Repair commands in SQL Server

Ms SQL Server provides many database repair commands that can be attempted when the database challenges or some cannot complete the read.

1. DBCC checkdb
After the server is restarted, if no operation is performed, execute the following SQL statement in the SQL query analyzer to repair the database and fix the consistency and allocation errors of 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' -- set the target database to single-user status
DBCC checkdb (@ databasename, repair_allow_data_loss)
DBCC checkdb (@ databasename, repair_rebuild)
Exec sp_dboption @ databasename, N 'single ', N 'false' -- set the target database to a multi-user State

Then run DBCC checkdb ('name of the database entity to be repaired ') to check whether the database is still faulty. Note: Some data may be lost after repair.

2. DBCC checktable
If DBCC checkdb check still has an error, you can use DBCC checktable to fix it.
Name of the database entity to be repaired in use
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 'name of the data table to be repaired name' to the name of the data table reported by DBCC checkdb.
Exec sp_dboption @ dbname, 'single user', 'false'

3. Other common repair commands
DBCC dbreindex rebuilding one or more indexes of a table in the specified database
Usage: DBCC dbreindex (Table Name, '') fixes all indexes in the table.

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

 

SQL Server database detection and repair methods
With the promotion of K/3 products, customer service personnel are required to learn more about SQL Server databases. During K/3 usage, database files are frequently used. For some reason, the database may be damaged, this article will give a brief explanation of the database detection and restoration methods in this case. We hope that you will provide us with information in time for further updates when there are new discoveries in your actual work.
1.1 SQL Server database Detection
SQL Server provides database detection commands. You can use DBCC checkdb to check the allocation and structure correctness of each object in the database. You can use one parameter to control the allocation, display All error messages. The 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' indicates the name of the database entity to be detected;
Noindex indicates that non-clustered indexes of non-system tables are not detected;
Repair_allow_data_loss | repair_fast | repair_rebuild indicates that the error is directly fixed. In this case, repair_allow_data_loss indicates that the system will directly Delete the relevant data if the error cannot be fixed. When either of the three parameters is included, the database must be in single-user mode and can be set in the database attributes of Enterprise Manager;
All_errormsgs indicates that all detected error messages are displayed. Otherwise, up to 200 error messages are displayed for each table;
No_infomsgs indicates hiding all information and space-consuming reports.
After detection, the error object will be reported in the form of an object ID. You can find the relevant table (name) in the system table sysobjects Based on the Object ID.

1.2 SQL server problem database repair
After database detection, You can take corresponding measures to solve the problems. If a problem occurs in the physical storage of the object after detection, you can use DBCC checkalloc to fix the problem:
DBCC checkalloc ('database _ name' | repair_rebuild}]) [with {all_errormsgs | no_infomsgs}]
If the index of a non-system object fails, you can use DBCC dbreindex to fix it:
DBCC dbreindex (['database. Owner. table_name '[, index_name [, fillfactor]) [with no_infomsgs]
You can use DBCC checkdb ('db _ name', repair_rebuild) to fix the preceding two cases.
In another case, the system prompts that a data connection cannot be established during the detection, indicating that the database is damaged. In this case, we can take the following measures to try to fix the problem.
First, create a new database in SQL enterprise (for example, the database name is test). After the database is created, stop SQL Server Service Manager and rename the MDF file of the customer database to test _ data. MDF (the master file name of the new database), overwrite the file with the same name of the new database with the renamed file, and then start SQL Server Service Manager. Set the system table to changeable status for the master database
Use master
Go
Sp_configure 'Allow updates', 1
Reconfigure with override
Go
Set the database to an emergency:
Update sysdatabases set status = 32768 where database'
Stop and restart SQL Server Service Manager and recreate the log file:
DBCC traceon (3604)
DBCC rebuild_log ('test', 'test _ log_ldf ')
Set the database to single-user mode and then perform Detection:
Sp_dboption 'test', 'single user', 'true'
DBCC checkdb ('test ')
Go
When the database executes the checkdb operation, it finds that the indexes of some tables are damaged. Therefore, it re-creates the indexes for specific tables:
DBCC dbreindex (table name)
If the above operations still cannot be solved, if the index damage table is a temporary table or not a key table, you can introduce it from the new ledger. If it is a master table, it may be recovered through recent backups. If there is no backup, it cannot be repaired.

1.3 why is the SQL Server database vulnerable to damage?
The following are some possible causes of database damage provided by Microsoft and some preventive measures:
Operation problems, including cold start machine, hot hard disk pulling, and deletion of some database files;
Hardware problems, including disk controllers;
Operating system problems, including system-related fatal errors.

1.4 preventive measures:
1. regularly or irregularly execute chkdsk (without parameters) to detect the physical structure of the hard disk and fix problems reported by chkdsk;
2. Data is often backed up.

1.5 application database repair example
Declare @ databasename varchar (255)
Set @ databasename = 'ais20021224170730 '------ be sure to manually enter
--------- Perform a general fix to allow data loss when there are still problems
--------- The repair of data loss must be performed under a single user. In this case, exit the middle layer, client, and other SQL modules.
--- All functions exit. Set the database as a single user in the query analyzer master.

Exec sp_dboption @ databasename, N 'single ', N 'true'

----- Repair the database in the query analyzer master
DBCC checkdb (@ databasename, repair_allow_data_loss)
DBCC checkdb (@ databasename, repair_rebuild)
------ Restore database status
Exec sp_dboption @ databasename, N 'single ', N 'false'

Chapter 4 repair of Database Log corruption
Follow these steps to try to recreate the database transaction log.

Note: Due to the loss of transaction logs, the database may have submitted data.

Note: Both must be replaced with 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 corresponding MDF file of the new database, and delete the LDF file.

2.4 Step 4:

Restart the SQL Server service and 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, restart SQL Server, and 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, restart SQL Server, and 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 database integrity.

Chapter 1 General handling of database challenges
1. Execute the following SQL statement (enable the system table modification function ):
Exec sp_configure 'Allow updates', 1
Reconfigure with override
2. Modify the table in the master database: sysdatabases
Change the value of the Status field to 4.
3. Execute the following SQL statement:
Exec sp_configure 'Allow updates', 0
Reconfigure with override.