How to fix SQL database

The database is damaged due to sudden power outages and other causes, prompting inexplicable errors, such as connection failure or an error in a system table. Such databases are divided into two types: one can be connected by a query analyzer, which can be repaired; the other is that the query analyzer cannot be connected, as far as I know, there is no solution to this problem.

However, the restoration term is not as complete as it was before, and may cause data loss or more or less problems. The following is my experience in database restoration. There are several steps. If the previous steps are correct, you do not have to proceed.

The first step is that a table is corrupted. The repair statement is:

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

-- Fix a table in the database
-- Fix it with the following statement:

Exec sp_dboption 'database name', 'single user', 'true'
Go
Dbcc checktable ('table name', repair_allow_data_loss)
Go
Sp_dboption 'database name', 'single user', 'false'
Go

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

Based on my experience, the chances of repairing user tables are relatively high, and the chances of repairing system tables are relatively small;

 

Step 2: you are not sure which table or table is the error, or any other error. Fix the error according to database corruption. The repair statement is:

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

-- Fix the entire database
-- Fix it with the following statement:

Alter database [DATABASE name] SET SINGLE_USER
GO
Dbcc checkdb ('database name', repair_allow_data_loss) WITH TABLOCK
GO
Alter database [DATABASE name] SET MULTI_USER
GO

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

 

Step 3: You can use the import/export function if you have tried either of the two methods.

The method is to create an empty table database, export the table structure and data from the damaged database, or generate an SQL script in the original database, in an empty database, the system first establishes a database structure and then only imports data. This has a higher success rate. An error message may be prompted during the import. For example, if an error occurs in a table, you can temporarily ignore this error and process it separately. This part can be flexibly processed.

Step 4: The Export and Import functions won't work either, that is, the error is always prompted and cannot be exported, so do this:

1. Create a blank database called database 1. Execute this data transfer statement in the original database: Select * into [database 1]. [DBO]. [Table name] from table name. This statement only transfers the table fields and data in the table, and does not copy the constraint indexes. Therefore, it can be copied in general;

2. Create the second blank database, called database 2. Use the SQL script of the original database to create the structure, and then use the Import and Export function to import the data of database 1 to database 2.

This method is generally applicable to databases.

Step 5: calculate how much data can be retained. If none of the above works, you can see what you can leave.