SQL SERVER Undelete possibility Exploration (i) Clustered Table

SQL Server, if the data is mistakenly deleted, according to the official statement, SQL Server is not undelete. To save the deleted data, the most typical way is to restore the data from another DB and then add it back to the formal db by restoring the backup file to another db.

However, if the transaction log structure of SQL Server is known to the person, as long as the transaction record is still, you can parse the transaction record to obtain the deleted data, and then insert the data back into the db. Just transaction log parsing is a bit complicated. This is also a topic worth exploring and interesting. If you can, maybe you can find time to talk about it.

In fact, there is another way ....

When SQL Server executes the delete, it does not really immediately erase the data from the page. It just "marks" it for deletion, which is logically deleted (not found), but actually the data still exists, when we call this record Ghost record.

Note:

SQL Server's "tags" are removed, and the clustered table and heap table tagging methods are not the same.

The real Data Purge (purge) is done by the Ghostcleanuptask system thread, which is woken up about every 5-10 seconds to really erase the ghost record. However, to avoid causing the system to be busy, it will only check or clear a limited number of pages at a time (it should be ten pages).

So from delete commit, to the data is really erased. There is a buffer period in the middle.

With such a buffer period, it gives us a chance to undelete.

First, the most important thing is that when a mistake is deleted, the following instructions must be executed as soon as possible to deactivate the Ghostcleanuptask. Lest the data be really erased.

DBCC TRACEON (661,-1)--Pause Ghostcleanuptask

Below we do a simple undelete test

Clustered Table Undelete Testing

Set up a test database, set up clustered index and non clustered index, and add 10 new data

Create DATABASE Testghost

Go

Use Testghost

Go

CREATE TABLE TESTTBL (c1 int identity primary KEY,C2 int, C3 varchar (10))

Go

Create INDEX idx1 on TESTTBL (C2)

Go

INSERT INTO TESTTBL values

(1, ' AAAA '), (2, ' aaaa '), (3, ' AAAA '), (4, ' AAAA '), (5, ' aaaa '),

(6, ' AAAA '), (7, ' AAAA '), (8, ' AAAA '), (9, ' AAAA '), (Ten, ' AAAA ')

Check the page condition of the table

EXEC master.dbo. [Getpagrowcount] ' Testghost ', ' testtbl ',-1

Note: Getpagrowcount is my self-written proc, just convenient to check the page data, you can also use DBCC page to check.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8C/3F/wKiom1hmDODwntlTAANfz-zrMMM171.png-wh_500x0-wm_3 -wmp_4-s_1536127385.png "title=" 2016-12-30_152900.png "alt=" Wkiom1hmdodwntltaanfz-zrmmm171.png-wh_50 "/>

Then delete the c1=5 data

Delete from Testtbl where c1=5

Execute select query, no data is found

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/3B/wKioL1hmDTeDzxjKAAFmoshl15o029.png-wh_500x0-wm_3 -wmp_4-s_2783633995.png "title=" 2016-12-30_153040.png "alt=" Wkiol1hmdtedzxjkaafmoshl15o029.png-wh_50 "/>

Suppose c1=5 to delete data by mistake, we begin to do undelete ...

Pause Ghost Cleanup Task

DBCC TRACEON (661,-1)

Use procedure to check the page information of the TESTTBL. You can see that the clustered page has a ghost record created with the index page. Since there is only one Ghsot record, we can almost confirm that the data that was mistakenly deleted is stored in PageID 78, which is also the target page to undelete. (This way of positioning, there may be inaccurate problems, the best way is through Fn_dblog () to obtain the PageID of the mistake, and then use this procedure, the results of the two, it will not be wrong)

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/8C/3F/wKiom1hmDhXDwplOAANdFqWVPuA462.png "title=" 2016-12-30_153214.png "width=" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:500PX;HEIGHT:114PX; " alt= "Wkiom1hmdhxdwploaandfqwvpua462.png"/>

After the PageID is determined, the next step is to determine the slotid of the pen data,

We look at its physical record through the DBCC page, as we can see from the Record_type. Slot 4 is ghost record

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8C/3F/wKiom1hmDqSxsaWvAAIBnDEibtY861.png-wh_500x0-wm_3 -wmp_4-s_408315618.png "title=" 2016-12-30_153650.png "alt=" Wkiom1hmdqsxsawvaaibndeibty861.png-wh_50 "/>

At this point we can determine PageID 78,slotid 4, is our goal to undelete.

This is a Clustered table, and its tag deletion method is to add the identified bits to the first byte of the row to mark the ghost record (the heap table is not the same).

------------------------------------------------------------------------------------------------

The first byte of row, starting from 0 to the 1th to 3rd digit from the right, translates to decimal, meaning the following:

0 (data record)

1 (forwarded record)

2 (a forwarding stub)

3 (Index record)

4 (Blob fragment or row overflow data)

5 (Ghost index Record)

6 (Ghost data record)

7 (Ghost version record)

-------------------------------------------------------------------------------------------------

Finally, using the binary editor, to find the DB PageID 78,slotid 4, the first byte of the ghost record recognition bit (decimal 6), changed to the normal data bit (decimal 0).

After the change, execute the Select query again, the data can be queried ...

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8C/3B/wKioL1hmDwiyiZXmAAGqW1ZMNbc292.png-wh_500x0-wm_3 -wmp_4-s_2279300904.png "title=" 2016-12-30_153834.png "alt=" Wkiol1hmdwiyizxmaagqw1zmnbc292.png-wh_50 "/>

I'll check again . page information, and found that it was still marked as Ghost Record

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/8C/3B/wKioL1hmD1bTol3-AANc-lC3c6I156.png "title=" 2016-12-30_153912.png "width=" "height=" 113 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:500PX;HEIGHT:113PX; " alt= "Wkiol1hmd1btol3-aanc-lc3c6i156.png"/>

Rebuild All Index ...

Alter index all on TESTTBL rebuild

Check page information again, it's normal.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8C/3B/wKioL1hmEAex5Ns3AANfXh8gp0k478.png "title=" 2016-12-30_154242.png "width=" "height=" 118 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:500px;height:118px; " alt= "Wkiol1hmeaex5ns3aanfxh8gp0k478.png"/>

However, such a modification will cause the System base table record to be inconsistent with the data page record, so there will be errors when executing DBCC CHECKTABLE.

The entire clustered Table undelete process, which must be performed at the end

DBCC CHECKTABLE (TESTTBL,REPAIR_ALLOW_DATA_LOSS)

Repair the data table, although it is used repair_allow_data_loss but it does not cause data loss.

Finally, don't forget to close flag 661.

DBCC TRACEOFF (661,-1)

SQL SERVER Undelete Test Success ~ ~

The above test, mainly in exploring the possibility of undelete, this test proves that undelete is possible. However, there is still some way to go in order to really use the operating environment of OLTP. (Great momentum, fast locating target pageid/slotid, downtime problem).

This article from "Sqlworker" blog, reproduced please contact the author!

SQL SERVER Undelete possibility Exploration (i) Clustered Table