First of all, I need to emphasize that this theme is to reveal the heap table delete record to retrieve the principle, I consider the aspect does not apply to each individual situation, hope everyone forgive ~
Many friends think that the database in simple mode, the heap table mistakenly delete a record, can not be retrieved, because there is no logging. In fact, in a sense, it can be recovered, because the heap table changed the row offset only when the record was deleted, the actual data was not physically deleted, so using this to test the recovery data was successful, but there was a problem with no results: If you do not turn off the page checksum, in addition to changing the offset, Delete the data also need to change the header, this is not time to ponder, so restore the data to be able to infer the header of the 16 of the corresponding relationship, interested friends can share the experience to me. Here to exclude the header checksum error, close the post test
No more nonsense to say that the test demo is as follows:
test Environment：
SQL Server 2008 R2
Database: repl_test Simple mode
Test table: Test_del
Test Steps
1. Create the test table Test_del and insert the test data.
Copy Code code as follows:
CREATE TABLE Test_del (a int identity,b char (10))
Go
Insert into Test_del select ' Row 1 ';
Insert into Test_del select ' Row 2 ';
Insert into Test_del select ' Row 3 ';
Insert into Test_del select ' Row 4 ';
Insert into Test_del select ' Row 5 ';
Go
2. Check the test data to show normal.
3.DBCC IND command to find the data page ID, find the data page id:219, this data page holds the Test_del data
To view the contents of a data page and the line offset using the DBCC page
DBCC page (repl_test,1,219,1)
The go output results are:
DATA:
Slot 0, Offset 0x60, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes = Null_bitmap record Size = 21
Memory Dump @0x00000000120cc060
0000000000000000:10001200 01000000 726f7720 31202020†........row 1
0000000000000010:20.2002 million 00†††††††††††††††††††††††††† ...
Slot 1, Offset 0x75, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes = Null_bitmap record Size = 21
Memory Dump @0x00000000120cc075
0000000000000000:10001200 02000000 726f7720 32202020†........row 2
0000000000000010:20.2002 million 00†††††††††††††††††††††††††† ...
Slot 2, Offset 0x8a, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes = Null_bitmap record Size = 21
Memory Dump @0x00000000120cc08a
0000000000000000:10001200 03000000 726f7720 33202020†........row 3
0000000000000010:20.2002 million 00†††††††††††††††††††††††††† ...
Slot 3, Offset 0x9f, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes = Null_bitmap record Size = 21
Memory Dump @0x00000000120cc09f
0000000000000000:10001200 04000000 726f7720 34202020†........row 4
0000000000000010:20.2002 million 00†††††††††††††††††††††††††† ...
Slot 4, Offset 0xb4, Length, Dumpstyle BYTE
Record Type = Primary_record record Attributes = Null_bitmap record Size = 21
Memory Dump @0x00000000120cc0b4
0000000000000000:10001200 05000000 726f7720 35202020†........row 5
0000000000000010:20.2002 million 00†††††††††††††††††††††††††† ...
OFFSET TABLE:
Row-offset
4 (0x4)-180 (0XB4)
3 (0x3)-159 (0x9f)
2 (0x2)-138 (0X8A)
1 (0x1)-117 (0x75)
0 (0x0)-(0x60)
Where the line offset first acts (0x60), the actual record is row 1,row 2: (0x75), row 3: (0x8a), row 4: (0x9f), row 5: (0XB4)
4. Delete third row of data A = 3,b = row 3 record
Copy Code code as follows:
Delete Test_del where a = 3
Go
Indicates that the record for A=3 b=row3 has been deleted.
5. View the row offset of the data page again
DBCC page (repl_test,1,219,1)
Gorow-offset
4 (0x4)-180 (0XB4)
3 (0x3)-159 (0x9f)
2 (0x2)-0 (0x0)
1 (0x1)-117 (0x75)
0 (0x0)-(0x60)
The line offset for line 3rd was found to be changed to 0 to continue execution
DBCC page (repl_test,1,219,2)
Godata:
..
00000000120cc060:10001200 01000000 726f7720 31202020†........row 1
00000000120cc070:20200200 00100012 00020000 00726f77†...........row
00000000120cc080:20322020 20202002 00001000 12000300†2 .....
00000000120cc090:0000726f 77203320 20202020 02000010†. Row 3 ....
00000000120cc0a0:00120004 00000072 6f772034 20202020†.......row 4
00000000120cc0b0:20020000 10001200 05000000 726f7720†...........row
00000000120cc0c0:35202020 20200200 00000021 21212121
found that ROW3 records also exist in the data page!
so guess, whether the third row of the line offset 0x0 modified back to the original 0x8a can restore the record?
With the Winhex tool, open the MDF file, because it is 219 pages, 8*220 = 1802240 bytes, so 219 of the line offset should be 1802239, the rest of the work is very simple
6. Close the Database data page I/O protection mechanism, that is, set the page_verify database option to None, and set the Repl_test database offline, using Winhex to find the Repl_test.mdf file at the end of the 1802240 16 code
Copy Code code as follows:
ALTER DATABASE repl_test set page_verify None
Go
Use master
ALTER DATABASE repl_test set offline
Go
Set the Repl_test database offline and use the Winhex tool to find the end of page 219 (220 pages in fact):
Sure enough, line 00 00 on line 3rd, then I'll change it back to 8A 00 and save it and set the database online
The record was successfully restored.
If you do not make
Copy Code code as follows:
ALTER DATABASE repl_test set page_verify None
Go
A page checksum error occurs when the table is read.
so how to retrieve the records can be DBCC CHECKDB security through it?
1. The stupid method retrieves the record to delete the original table, the damaged page will be lost, the table, the import data can.
2. Modify the header check, unfortunately, the younger brother is not, has not studied the header structure corresponding to the physical 16-system relationship. Only by the change before the header screenshot, modified according to the screenshot to restore the header, here can not say to everyone understand the change of place. Hope to have experience or interested friends can share with me, thank you ~
How do I free a free page in the heap?
To remove rows from the heap and release the page, we can use one of the following methods.
• Specify the TABLOCK prompt in the DELETE statement. Using the TABLOCK hint causes the delete operation to get a shared lock on the table, not a row or page lock. This will allow the page to be freed.
• If you want to remove all rows from the table, use TRUNCATE table.
• Create a clustered index on the heap before you delete rows. After you delete a row, you can delete the clustered index. This method is time-consuming and uses more temporary resources than previous methods.
If free page space is released, it is very likely that the record can no longer be restored, and that the database Full mode + log backup is so important that you save a lot of miscellaneous steps
Writing is not good, if where to see the blur please leave a message.
