SQL Server database error data Recovery-database data recovery

1. List of server hardware devices


2. Fault description
The server that needs data recovery is a r520 model store, with a total of 7 SAS hard disks comprising RAID1 and RAID5 two sets of disk arrays respectively. The main SQL Server database is stored in the C disk, in the process of use, the customer found that the capacity of the C disk is about to be full, so the database path to the D disk, the D disk generated a. ndf file.
After the customer continues to use for about 10 days, the database fails, the connection fails, and the query cannot be attached properly.

3. Backing up data
Taking into account the security and recoverability of the data, it is necessary to make a backup of all the source data before the data is restored, just in case the data cannot be recovered again for other reasons. Use the DD command or the Winhex tool to mirror all the disks into files.

4. Failure Analysis
a) analyze the cause of the failure
A logic error occurred due to insufficient disk capacity on the database file to allow the database to continue to function properly
(b) Analysis of RAID group structure
The client server has a total of 7 300G hard disks, of which 2 drives are RAID 1, which installs the operating system, and the remaining 5 drives do RAID 5 storage data.
Analyze the structure of RAID 1 and RAID 5, and reorganize virtual out RAID 1 and RAID 5 to see the data.
(c) Analysis of raw database files
Because the client has made multiple database recovery attempts after a database failure, and every attempt is made in the source environment, the original database file is overwritten with changes, and the disk space is repeatedly replicated and cannot be repaired using the database file after the recovery attempt.
Ask the customer to know that the customer in the database failure, back up a point of the original fault database files.

5. Database Repair
Copy the database files that the customer backed up from the virtual RAID 5 space, attempt to attach in the database, attach failed, error message is as follows:
Figure One:

Error message the primary and secondary database files do not match, look at the bottom of the. ndf file, find that there is little data in the. ndf file, try to disassociate the. mdf file from the. ndf file, and attach it only with an. mdf file.
An error occurred while attempting to attach with an. mdf file, but the error message changed
Figure II:

The error message log file (. ldf) and the database file (. mdf) do not match at this time.
After the database attempt is made without database attach, the attach succeeds. However, it was found that the database system tables were corrupted and could not be used properly.
Might

The system tables on the database tried to repair, but could not be repaired because the system tables were damaged too badly.
Database Record Extraction
Parse database records in a database file
Write the appropriate program to extract the database records from the database file
Get the table structure in the database based on the customer's previous database backup
The refactoring table structure extracts the database records into a new table

6. Data validation
The extracted database records are validated by the customer, all data is fully recovered and the data is restored successfully.

7. Data Recovery Conclusions
In the process of database use, it is reasonable to allocate the disk space of the database files and clean up the garbage data in time to ensure the normal and safe operation of the database.

SQL Server database error data Recovery-database data recovery