Shulou(Shulou.com)05/31 Report--

Database ndf file size changed to 0KB how to carry out data recovery, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

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. Scan the disk and scan for database fragments

2, splicing fragments; according to the page characteristics of NDF files, splicing fragments according to the 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. Based on the 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 and report an error. "an error occurred while 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 is attached to the database; modify the system table, remove the LDF file added from the system table, and 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 recorded in the system table to the number of blocks of the four NDF files scanned out, 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 are consistency errors 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.

After reading the above, have you mastered the method of data recovery when the ndf file size of the database is changed to 0KB? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.