Common MS SQL Server data repair commands

MS SQL Server provides a number of commands for database repairs that can be attempted when the database is challenged or some cannot complete the read.

1. DBCC CHECKDB

After restarting the server, perform the following SQL database repairs in SQL Query Analyzer without doing anything, fixing the consistency errors and allocation errors in the database.

Use master

DECLARE @databasename varchar (255)

Set @databasename = ' Name of the database entity that needs to be repaired '

exec sp_dboption @databasename, n ' single ', n ' true '--set the target data base to Single-user state

DBCC CHECKDB (@databasename, Repair_allow_data_loss)

DBCC CHECKDB (@databasename, Repair_rebuild)

exec sp_dboption @databasename, n ' single ', n ' false '--set target data base to multiuser state

Then perform the DBCC CHECKDB (the name of the database entity to be repaired) to check that the database is still in error. Note: The repair may result in some loss of 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 you want to fix

DECLARE @dbname varchar (255)

Set @dbname = ' Name of the database entity that needs 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 to be repaired ' to the name of the data table that executes the DBCC CHECKDB times error

exec sp_dboption @dbname, ' Single user ', ' false '

3. Some of the other common repair commands

DBCC Dbreindex rebuilds one or more indexes of a table in the specified database

Usage: DBCC dbreindex (table name, ') fixes all indexes in this table.

There are many other fixes, such as DBCC checkident and so on, that are less likely to be used and can be retrieved in the MS SQL Server Books Online.