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

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

Recent period of time, Jinan with Yue technology receive a lot of customer inquiriesSQL database corruption ,additional database error problems, here to tidy up a few of the common sqlsever DatabaseFailure PhenomenaandPrecautions .

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >       current major small and Medium business use sqlsever There are a lot of applications, but for a variety of reasons, there are often a few different failures, and there are several common ones:  

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

650) this.width=650; "title=" Picture 1.jpg "src=" http://s5.51cto.com/wyfs02/M00/7D/C6/ Wkiom1bvmwqieomgaabfw8ftvrw248.jpg "alt=" Wkiom1bvmwqieomgaabfw8ftvrw248.jpg "/>

cause of failure:

( 1 crashes or loses power when the database reads and writes.

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >      2 ) server restart, after reboot the database appears " suspect " status. &NBSP;

( 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.

Two, 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.

650) this.width=650; "title=" Picture 2.jpg "src=" http://s2.51cto.com/wyfs02/M02/7D/C6/ Wkiom1bvmycjjfosaaglvr3pu7e971.jpg "alt=" wkiom1bvmycjjfosaaglvr3pu7e971.jpg "/>

cause of failure:

( 1 The backup files and databases are placed on the same physical hard disk, the hard drive fails, and the backup is corrupted.

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >      2 ) The backup media is corrupted, or the network backup is done, the data is corrupted in the network transmission.

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >      3 ) the contents of the database are corrupted when they do a full backup, file backup, or filegroup backup. This is because sql server recover sql server and undo)

( 4 when you back up the database because there are bad lanes in the disk, the backup MDF This error can also occur when the file is incomplete.

When the database above failure, you need to repair the database files in the bad page, if the MDF The document structure is not very clear, please do not modify the original document, this will be counterproductive, will cause greater losses. 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.

when the database is attached or is far from successful, DBCC CHECKDB Not to pass, to report a series of mistakes. As shown below:

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >      checkdb    was found; 5  allocation error and &NBSP; 2  a consistency error (in database   ' test '  

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >      repair_allow_data_loss dbcc checkdb  (test )   Span style= "font-family: ' Microsoft Jas Black '; > the error found). DBCC&NBSP; execution is complete. if   output error message, Contact your system administrator

cause of failure: a page of the database has been changed or 0 , resulting in consistency errors and allocation errors.

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

in the detection database, the following error is common:"Server: message8966, Level -, the State1, Line1failed to read and latch page(1:4234)(with latch typeSH). sysobjectsfailed". This"failed to read and latch page(1:4234)(with latch typeSH)"errors often appear in system tables:sysobjects,sysindexes,syscolumnsand so on, this error occurs because the system table is corrupted, and this error is cumbersome becauseSQLthe effectiveness of the more rigorous, as long as a small change of a key byte, all reported this error, but sometimes you can export some data.

Five, mistakenly deleted or mistakenly formatted.

< Span style= "Font-family:arial;color:rgb (0,0,0); font-size:14px;" >       mdf and Span style= "font-family:arial;" >ldf file, but almost will not be attached, even if the attachment succeeds, there will be a lot of errors, because the database in the daily increase and delete records frequently, so that the database file storage is not contiguous , and the software on the market is continuously fetching data, so 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 more complete recovery database in-table records .

650) this.width=650; "title=" qq20160321133345.jpg "src=" http://s2.51cto.com/wyfs02/M00/7D/C2/ Wkiol1bvm9cygeraaapnvct7-1m427.jpg "alt=" Wkiol1bvm9cygeraaapnvct7-1m427.jpg "/>

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

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