Restore SQL Server simple mode by mistake Delete heap table records

Many friends think that in simple mode, the heap table cannot retrieve a record because there is no log record. In fact, in a sense, it can be recovered, because the heap table only changes the row offset when deleting records, and the actual data is not physically deleted, after the test, the data recovery is successful, but some problems have not been studied: if you do not close the page verification, in addition to changing the offset, you also need to change the header when deleting the data, there is no time to think about this, so we need to be able to break out the hexadecimal relationship of the header when we restore the data. If you are interested, you can share your experience with me. In order to exclude the header validation error, close and Test

The test demo is as follows:

Test environment:

SQL Server 2008 R2

Database: repl_test simple mode

Test Table: test_del

Test procedure

1. Create the test table test_del and insert the test data.

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. The data is displayed normally.

3. Run the dbcc ind command to find the data page id and find the data page id: 219, which stores the data of test_del.

Use dbcc page to view the data page content and row offset

 

 

	
	
  
 dbcc page(repl_test,1,219,1)   

	
  
 go  


 

Output result:

DATA:

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @ 0x00000000120CC060

0000000000000000: 10001200 01000000 running f7720 31202020 running ...... row 1
0000000000000010: 20200200 ††††††††††††††††††††††††††...

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @ 0x00000000120CC075

0000000000000000: 10001200 02000000 running f7720 32202020 running ...... row 2
0000000000000010: 20200200 ††††††††††††††††††††††††††...

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @ 0x00000000120CC08A

0000000000000000: 10001200 03000000 running f7720 33202020 running ...... row 3
0000000000000010: 20200200 ††††††††††††††††††††††††††...

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @ 0x00000000120CC09F

0000000000000000: 10001200 04000000 running f7720 34202020 running ...... row 4
0000000000000010: 20200200 ††††††††††††††††††††††††††...

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @ 0x00000000120CC0B4

0000000000000000: 10001200 05000000 running f7720 35202020 running ...... row 5
0000000000000010: 20200200 ††††††††††††††††††††††††††...

Offset table:

Row-Offset
4 (0x4)-180 (0xb4)
3 (0x3)-159 (0x9f)
2 (0x2)-138 (0x8a)
1 (0x1)-117 (0x75)
0 (0x0)-96 (0x60)

Where the first row offset is 96 (0x60), the actual record is row 1, row 2: (0x75), row 3: (0x8a), row 4 :( 0x9f ), row 5: (0xb4)

4. delete records with data of the third row a = 3 and B = row 3.

 

 

	
	
  
 delete test_del where a = 3   

	
  
 go  


 

It indicates that the record a = 3 B = row3 has been deleted.

5. view the row offset of the data page again

 

 

	
	
  
 dbcc page(repl_test,1,219,1)   

	
  
 go  


 

Row-Offset
4 (0x4)-180 (0xb4)
3 (0x3)-159 (0x9f)
2 (0x2)-0 (0x0)
1 (0x1)-117 (0x75)
0 (0x0)-96 (0x60)

The row offset of row 3rd is changed to 0.

 

 

	
	
  
 dbcc page(repl_test,1,219,2)   

	
  
 go  


 

DATA:

201710000120cc060: 10001200 01000000 109f7720 31202020 then ...... row 1
201710000120cc070: 20200200 00100012 00020000 0020.f77 then ...... row
201710000120cc080: 20322020 20202002 00001000 12000300 20172 .........
201710000120cc090: 00000000f 77203320 20202020 02000010 queries... row 3 ....
201710000120cc0a0: 00120004 00000072 6f772034 20202020 then ...... row 4
201710000120cc0b0: 20020000 10001200 05000000 2017f7720 then ........ row
2017-12120cc0c0: 35202020 20200200 00000021 21212121

We found that records of row3 still exist on the data page!

So I guess whether to change the row offset 0x0 of the third row back to the original 0x8a to restore the record?
 

Use the winHex tool to open the mdf file. Because it is a 219 page, 8*220 = 1802240 bytes, the line offset of 219 should be at 1802239. The rest of the work is very simple.

6. disable the data page I/O protection mechanism of the database, that is, set the page_verify database option to none, and set the repl_test database to offline. Use winhex to find the hexadecimal code at the end of the 1802240 repl_test.mdf file.

alter database repl_test set page_verify none  
go  
use master   
alter database repl_test set offline  
go 

Set the repl_test database to offline, and use the winhex tool to find the actual location of the 219 page at the end of the 220 page ):

Sure enough, the row offset of row 3rd is 00, so I will change it back to 8A 00, save it, and set the database to online

The record is successfully restored.

If not

Alter database repl_test set page_verify none
Go

A page verification error occurs when the table is read.

So how can we retrieve records and pass DBCC checkdb safely?

1. You can easily retrieve the record and delete the original table. Damaged pages will be lost. re-import the table and import the data.

2. Modify the header verification. Unfortunately, the younger brother did not study the physical hexadecimal relationship corresponding to the header structure. You only need to modify the original header, and then follow the instructions to restore the header. Here, we cannot explain the modified content to you. Hope you can share your experience or interest with me. Thank you ~

Edit recommendations]

1. Performance testing of Several SQL statements with millions of data
2. Use the database connection monitoring component to solve the shutdown Problem
3. High Availability of SQL Server
4. SQL Server 2008 R2 failover Cluster Environment preparation
5. SQL Server: how local variables affect query performance