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

1. Fault description of MSSQL ndf file

Some storage of the user is corrupted, causing the database to crash. By reorganizing the storage and restoring the database files, it was found that four ndf files changed in size to 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 of MSSQL ndf files.

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

III. MSSQL ndf file 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 a database

Error reporting error processing the log of the database, restore from backup if possible. If no backup is 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: there is a consistency error in the database, as shown below:

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 are consistency errors in the error reporting database, as shown in the figure below.

9 modify the number of blocks of the four corrupted NDF files in the system table one by one

Make its value equal to the analysis error on the previous page of the error block. Because the empty pages all appear in more than a dozen blocks behind the four NDF, the truncated file has little effect 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"

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

4. MSSQL ndf file data recovery results

After the database file is successfully attached, the user 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. At this point, the data recovery work is over.

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.