MSSQL ndf file size becomes 0 KB the process of recovering data

First, fault description

A customer in Chengdu, storage corruption, database crashes. Reorganize the store, recover the database file, and find that four ndf file size becomes 0 KB. The database size is approximately 80TB. There are 1223 files in the database, the database generates a NDF file every 10 days, each ndf is about 500GB, and the database contains two LDF files.

Second, fault analysis

Storage corruption, the NDF file size becomes 0 KB and may exist on disk depending on the NDF file. You can write a database scan fragment program, scan database fragments, stitch up fragments to recover NDF files, and then repair the database.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/97/E3/wKioL1k0vvexS3aAAAKj4ns6aAk665.jpg-wh_500x0-wm_ 3-wmp_4-s_214032969.jpg "title=" timg (1). jpg "alt=" wkiol1k0vvexs3aaaakj4ns6aak665.jpg-wh_50 "/>

Iii. recovery process

1 disk scan, scan database fragmentation

2 Stitching Fragments

According to the page characteristics of the NDF file, according to the file number, page number splicing fragments, the reorganization generated these 0kb ndf files.

3 Detecting database files

Using North Asia MSSQL File Detection Tool to detect all data files, the result: 4 ndf files are stitched up with a small number of empty pages, other files are normal.

4 further analysis of the damaged LUNs reveals that the data pages are no longer present at the storage level. These data pages cannot be recovered, i.e. these 4 files cannot be fully recovered

5 attempt to attach the database, error "Errors occurred while processing the log of the database, if possible, restore from backup." If you do not have a backup available, you may need to regenerate the log. "

6. No log attached database

Modify the system tables, remove the last LDF file from the system table, and calculate and modify the checksum. Perform a no-log attach database. Error: There are consistency errors in the database,

Like:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/97/E3/wKioL1k0vr3jwE8pAACyZ569qOY754.jpg-wh_500x0-wm_ 3-wmp_4-s_932891287.jpg "title=" 1.jpg "alt=" Wkiol1k0vr3jwe8paacyz569qoy754.jpg-wh_50 "/>

7 Modify the number of blocks of these 4 corrupted NDF files in the system table so that they match the number of blocks of the recovered files

Modify the system table, the system table records the value of the 4 NDF block number to the same number of files scanned ndf, and change the first page of the 4 ndf files, so that the number of files recorded in the database and the number of blocks of the NDF block the same amount, calculate and modify the checksum value.

8 No log attached database, error database There are consistency errors,

As two:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/97/E1/wKiom1k0vsnA7puvAACyZ569qOY579.jpg-wh_500x0-wm_ 3-wmp_4-s_990535520.jpg "title=" 2.jpg "alt=" Wkiom1k0vsna7puvaacyz569qoy579.jpg-wh_50 "/>

9 Modify the number of blocks of these 4 corrupted NDF files in the system table one after the other to make their values equal to the previous page of the error block

Analysis error, because empty pages appear in the 4 ndf behind more than 10 blocks, truncation of files on the data integrity is not very significant. Re-modify the system tables and the NDF file to change the value of the number of NDF blocks in the database to the previous page of the error, and to calculate and modify the checksum.

10, re-log additional database, error "because the database is not completely shut down, unable to regenerate the log"

such as three:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/97/E3/wKioL1k0vtaA4iwXAABuZQAoraU961.jpg-wh_500x0-wm_ 3-wmp_4-s_1844008541.jpg "title=" 3.jpg "alt=" Wkiol1k0vtaa4iwxaabuzqaorau961.jpg-wh_50 "/>

11. Modify the state value of the database in the MDF file so that the database is considered to be completely closed

12, re-attach the database, attach success

such as four:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/97/E3/wKioL1k0vuTAmAemAACJ-01QvTI505.jpg-wh_500x0-wm_ 3-wmp_4-s_2187827468.jpg "title=" 4.jpg "alt=" Wkiol1k0vutamaemaacj-01qvti505.jpg-wh_50 "/>

Iv. Results

After the database file is attached successfully, the client makes preliminary query and verification through the object in the database, the information in the table is basically complete and the data is restored. After the data has been verified, the customer is satisfied with the recovery result.

This article is from the "SUN" blog, be sure to keep this source http://sun510.blog.51cto.com/9640486/1932182

MSSQL ndf file size changed to 0 KB recovery data