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, according to the file number, page number splicing fragments, reassemble to generate these 0kb NDF files.

3 detect database files

Use the North Asia MSSQL file detection tool to detect all data files, the results: the spliced 4 NDF files have a small number of empty pages, 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 is as follows:

7 modify the number of blocks of the four corrupted NDF files in the system table to match the number of blocks of the recovered files

Modify the system table, change the value of the number of blocks recorded in the system table to the number of blocks in the scanned NDF files, and change the home page of the four NDF files to make the number of blocks recorded in the database consistent with the number of blocks in the spliced NDF, and 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 is as follows:

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

The error analysis shows that because the empty pages all appear in more than a dozen blocks behind 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 is as follows:

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 is as follows:

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.