Shulou(Shulou.com)06/01 Report--

Fault description

A customer in Chengdu, the storage is damaged and the database crashes. Reorganize the storage, restore the database files, and find that there are four ndf files with a size of 0 KB. The database size is about 80TB. There are 1223 files in the database, and the database generates a NDF file every 10 days, each NDF is about 500GB, and the database contains two LDF files.

Second, fault analysis

The storage is corrupted and the NDF file size changes to 0 KB, which may exist on disk depending on the NDF file. You can write a database scan fragment program, scan database fragments, splice fragments to recover NDF files, and then repair the database.

III. Recovery process

1 disk scan, scan for database fragments

(2) splicing fragments; according to the page characteristics of NDF files, splicing fragments according to file number and page number, reassembling to generate these 0kb NDF files.

(3) detect the database files; use the North Asia MSSQL file detection tool to detect all the data files, the results: the spliced 4 NDF files have a small amount of empty pages, and the other files are normal.

4 further analysis of the damaged lun, it is found that these data pages no longer exist at the storage level. These data pages cannot be restored, that is, the four files cannot be fully restored.

5 try to attach database, error "error processing the log of the database, restore from backup if possible. If there is no backup available, you may need to regenerate the log".

6. No log attached database; modify the system table, remove the last added LDF file from the system table, calculate and modify the check. Attach no log to the database. Error report: database consistency error

Figure 1:

7 modify the number of blocks of the four damaged NDF files in the system table to make them consistent with the number of blocks of the recovered files; modify the system table, change the value of the number of blocks of the four NDF files recorded in the system table to be the same as the number of blocks of the scanned NDF files, and change the front page of the four NDF files to make the number of blocks recorded in the database consistent with the number of blocks of the spliced NDF file, calculate and modify the check value.

8 there is no log attached to the database, and there is a consistency error in the error reporting database

Figure 2

9 modify the number of blocks of the four corrupted NDF files in the system table one by one so that the value is equal to the page before the error block; analyze the error, because the empty pages appear in more than a dozen blocks after the four NDF, the truncated file has little impact on data integrity. Re-modify the system table and NDF file, change the value of the number of NDF blocks recorded in the database to the page before the error report, calculate and modify the check.

10. Restart the non-log attached database and report an error: "the log cannot be regenerated because the database is not completely closed"

Figure 3:

11. Modify the status value of the database in the MDF file to make the database think it is completely closed

12. Reattach the database successfully

Figure 4:

IV. Results

After the database file is successfully attached, the customer makes a preliminary query and verification through the objects in the database, the information in the table is basically complete, and the data is restored. After the verification of the data, the customer is satisfied with the recovery result.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.