What about SQL database corruption? Teach you the data recovery contingency plan

Recently, Jinan with Yue Technology received a lot of customer consultation SQL database corruption, additional database error problems, here to tidy up the Sqlsever database some common fault phenomena and precautions.

At present, the large and medium-sized enterprises use Sqlsever application is very much, but for various reasons, also will often appear a number of different failures, common there are several:

One, the additional database file MDF and log file ldf, reported "823" error.

Cause of failure:

(1) in the database read and write process suddenly crash or power outage.

(2) The server restarts and the database has a "suspect" status after rebooting.

(3) disk I/O error

In the above possible three kinds of sudden failure, due to the loss of buffered data, the database can not write the correct data, resulting in data structure disorder, after restarting the database can not be properly attached.

Second, an "internal consistency error" occurs when a database restore is made from a previously backed up database.       This is often the database manager's biggest nightmare, obviously did a backup, but in the restoration found that the backup file is corrupted. This means that the database is missing and the consequences are very serious.

Cause of failure:

(1) The backup file and the database are placed on the same physical hard disk, the hard disk fails, and the backup is damaged.

(2) The backup media is damaged, or the network backup is done, the data is damaged in the network transmission.

(3) When the database is doing a full backup, file backup or file group backup, the contents are already corrupted. This is because SQL Server is doing data backup in order to save time, basically just very simple to copy the data page, do not do consistency check. However, when recovering, you need to restore the database (Recover) to a point in time when the transaction is consistent. If the corruption in the backup prevents SQL Server from rolling back (redo and undo), the recovery action encounters an error.

(4) This error can also occur when backing up the database because there is a bad path on the disk and the backed-up MDF file is incomplete.

When the above failure of the database, you need to repair the database files in the bad page, if the structure of the MDF file is not very clear, please do not modify the original file, this will be counterproductive, will cause greater loss. We can first back up the database files, and then the backup file with the command to repair, often this situation after the command repair, the data will be lost part!

Third, after the additional restore database, the detection database is a consistency error and allocation error

Database attached or is far from successful, DBCC CHECKDB, reported a series of errors. As shown below:

CHECKDB found 5 allocation errors and 2 conformance errors (in database ' test ').

Repair_allow_data_loss is the lowest level of repair (for errors found by DBCC CHECKDB (test)). DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

Failure occurs because a page of the database is changed or cleared 0, resulting in a consistency error and allocation error.

Four, the most common "failed to read and latch page (1:4,234) (with latch type SH)"

In the detection database, it is common to the following error: "Server: Msg 8966, Level 16, State 1, line 1 failed to read and latch page (1:4,234) (with latch type SH). Sysobjects failed. " This "failed to read and latch page (1:4,234) (with latch type SH)" Error often occurs in system tables: sysobjects, sysindexes, syscolumns, etc., this error occurs because the system table is destroyed, this error is very troublesome, Because SQL is more rigorous, as long as a small change of a key byte, are reported this error, but sometimes you can export some of the data.

Five, accidental deletion or misuse of formatting

In this case, many users will use some software on the market to recover the database, although the use of these database software can recover the MDF and LDF files, but almost can not be attached, even if the attachment succeeds, there are many errors, because the database in the daily increase and deletion of records, In this way, the database file is stored in a discontinuous situation, and the software on the market is continuously fetching data, it will cause the database can not be attached. When this error occurs, the user should try not to use this computer, not to install software and write any data.

For the last two cases of database corruption, recovery is relatively complex, some simple command recovery is not up to the purpose of recovery, which requires a very deep understanding of the structure of the database. Currently on the market, there are very few tools to recover the database intelligently, users encounter this problem can try to use D-recovery for MS SQL Sever Database recovery special software through the way of fragment extraction to more complete recovery database in-table records.

Important reminder: The data is priceless, please do a regular backup!

What about SQL database corruption? Teach you the data recovery contingency plan