Use of the tool Log Explorer (restore a database that is accidentally deleted without backup)

Original post address: http://expert.csdn.net/Expert/topic/2931/2931668.xml
Because the forum cannot restore more than 30 posts, this post is newly created.
First of all, I would like to thank the enthusiastic help of lynx1111, leeboyan, and realgz. I would also like to thank islandnet (E rabbit), zhijiao (Tutorial), and kkkksunday (I am a pig younger brother) shuichangliu (life will surely get better and better !) Ghostzxp, terencegan, progress99, outwindows, benimarunikado )...... With enthusiastic attention from others, the databases covered by incorrect operations have basically been completely restored. After the recovery site re-run, welcome to visit [Address http://www.gbq.cn]

Now I will report the database recovery process and several problems I encountered here, hoping to help you with the same problem:

[Fault Cause:]
When you use the data import and export tool to transfer a local table to the server, forget to click "select all objects ", therefore, more than 140 pieces of data in the remote 10 million tables are overwritten (the operator did not cancel the operation after the submission is completed), and the database was not backed up.

[Recovery Process:]
The tool used is log‑e, (: http://five.ttdown.com/L/Log%20Explorer%20for%20SQL%20Serverv%20v3.21.KG.exe)

After installation, open Log Explorer File => attach log file-> Select Server and login mode-> connect->
Select database> attach> browse> view log> In the left-side dialog box to view the log record,
Click View DDL commands. There are many drop table commands.
Click the "undo" button below to generate the table structure Statement (Create Table ....)
Click the "Salvage" button below to generate the insert Statement (insert into... values ....)
(Provided in the above versions by lynx1111)

I generate the insert statement for the deleted table according to the "Salvage" method described above. In fact, the SQL script generated using this method already contains createtable. It took about 8 hours to complete the process. At that time, I felt that the process was slow. Later, it was not as fast as the restoration process. The maximum table script size exceeds 1 GB after being generated.

After all the SQL scripts are generated, the database should be stopped and the log and. copy the MDF file (for fear of damaging the log file, and do not use the database backup mode). The total file size is 5.7 GB.

After that, the formal recovery will begin. To create a new database, run the script for a small table using the SQL query analyzer. However, when a large SQL script file is imported, the query analyzer reports an error. I consulted realgz to learn that logexplorer has good support for big scripts. Therefore, I used the logexplorer -- run SQL Script Function to run the script. Sure enough, the large file can be restored.

However, after running, it is found that tables with ntext fields are recovered abnormally slowly. Open a recovery script for a table with ntext fields and use writetext to write data. It took nearly 12 hours to restore a 0.3 million data table, and a large number of such tables exist in the Database. To speed up data, I installed logexplorer on several other machines to join the restoration process. Finally, after three days, all the tables were almost done, but there were a few errors in the restoration process.

Next, I will export tables from several machines to the same database. However, the recovered tables do not contain index, identity, and other extended attributes, therefore, you need to re-create indexes, identifiers, default values, and triggers. When a primary key is created, it is found that there are actually data duplicates... You cannot delete duplicate data.

You can use select distinct * into t_new from t_old to delete duplicate data. However, this method cannot be used in tables with ntext fields, finally, delete from t_table where ID in (select ID from t_table A where (select count (*) from t_table A where. id = ID)> 1) records with duplicate data are deleted directly.

After 72 hours of efforts, 99.9% of data is restored. The website was resumed on the evening of April 9, April 8.

At this time, some users reported that they could not log on. A small amount of data was lost, that is, the data reported in logexplorer ...... No way. I re-use uedit to open the SQL script and search for the data. I found it was still there. I checked it carefully and found that a large number of carriage returns were used in some of the data, which could not be identified by logexplorer, therefore, an error occurs.

Haha, the customer is God and there is no way, so we have to restore the user table to the local machine again. In case of an error, we will record the ID and then test the SQL script to run the query analyzer (the query analyzer can run)

Now we have established a maintenance plan to make a full backup every week. In addition, the database operation process is also standardized to prevent such incidents.

[Some gains:]
1. Use the text/ntext field with caution
2. The script execution tool of logexplorer is good at dealing with large files, but the execution process will produce incorrect judgments for multiple carriage returns.
3. Don't worry if you have any questions. Go to csdn and ask experts for help. They will be very enthusiastic to help you.