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

This article mainly explains "Sql Server database file loss how to recover", the explanation content in the article is simple and clear, easy to learn and understand, please follow the idea of Xiaobian slowly in-depth, together to study and learn "Sql Server database file loss how to recover" bar!

Fault Description:

5 blocks of 2T hard disks form RAID 5, dividing LUNs for use by Windows servers. Sql Server2008 database is installed in Windows Server. There are three logical partitions in the storage space, with sizes of 500G, 800G and 2.3T respectively. Database file loss, mainly involving five databases, the number of tables is about 6000. The cause of the loss is unknown and the data storage location cannot be determined. The size of the three databases is 8G, 15G and 20G respectively. The server is still on after the file loss, but not writing a lot of data.

Initial inspection process:

Reorganize RAID using RAID information and internal block information.

Reorganize RAID

2. Extract the three partition mirrors within the LUN.

3. Scan the file system for missing files and find no deleted database files.

4. The initial inspection result is that the database file is lost and cannot be recovered from the file system angle.

Recovery process:

1. Develop a recovery plan. After a database file is deleted and determined to be unrecoverable, it can only be recovered by scanning the data page and extracting the records in the page.

2. Use North Asia self-compiled data page scanning program to scan and extract data pages in partitions. After scanning two partition mirrors respectively, it is found that the number of data pages in the 500G system disk is very small and the data page fracture is serious, and the number of data pages scanned in the other partition is large. This partition is tentatively designated as database file storage space.

scanning data page

3. Reorganize the system table. Sql Server database uses system tables to manage all user tables, in which the column number, data type and constraint information of each table are recorded. During parsing system table, it was found that system table in extracted data page was damaged, and information could not be read normally. After communicating with customers, I learned that there are backup files, and there are no large changes to the table structure after the backup is completed, and the system table is available.

4. Restore backup.

restoring a backup

5. Extract the table structure information of each table in the three libraries respectively.

Extract table structure information

6. Parse the table structure script. The column information of each table is stored in the database for subsequent use.

Scan script files

Table structure information stored in database

7. Parse the system table to obtain user table ID information, association table structure and data page. (In order to protect customer privacy, the following steps involve the user table name and the data part in the data page are not captured)

8. Create a new database, use Beiya's self-written software to parse records and import them into the recovery environment.

9. Organize the recovery results. In addition to database files, there are several backup files in this partition, so there may be duplicate data after exporting records, which must be deduplicated. Write SQL stored procedures for deduplication.

database deduplication

10. Customer verification data. After checking the data, the customer indicates that the data can be accepted, hands over the data to the customer storage device, and recovers successfully.

Thank you for reading, the above is "Sql Server database file loss to how to recover" the content, after learning this article, I believe we have a deeper understanding of how to recover Sql Server database file loss, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.