Shulou(Shulou.com)06/01 Report--

In SQLSERVER, if the data is deleted by mistake, according to the official statement, SQLSERVER does not have undelete. The most typical way to save deleted data is to restore the backup file to another DB, retrieve the data from another DB, and then add it back to the formal DB.

However, people who know the transaction log structure of SQLSERVER can get the deleted data by parsing the transaction record as long as the transaction record is still there, and then Insert the data back to DB. It's just that the parsing of transaction log is a little complicated. This is also a topic worth discussing and interesting. If you can, maybe we can talk about it sometime.

In fact, there is another way... .

When SQLSERVER executes the delete, it doesn't really erase the data from the page immediately. It just "marks" it for deletion, which is logically deleted (not available), but in fact the data still exists, and we call this record Ghost record.

Note:

The "tag" deletion of SQLSERVER is different in clustered table and heap table.

The real purge is done by the GhostCleanuptask system thread, which is awakened about every 5 to 10 seconds to actually clear the Ghost record. However, to avoid keeping the system busy, it only checks or clears a limited number of pages at a time (which should be 10 pages).

So from delete commit to the data is actually erased. There is a buffer period in between.

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

First and foremost, when an erroneous deletion occurs, the following instructions must be executed as soon as possible to deactivate Ghostcleanuptask. In case the data is actually erased.

Dbcc traceon (661 Maxim 1)-pause Ghostcleanuptask

Let's do a simple Undelete test.

Clustered Table undelete Testing

Set up a test database, set up clustered index and non clustered index together, and add 10 pieces of 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)), (2)), (3)), (4)), (5)

(6)), (7)), (8)), (9)), (10)

Check the page status of the table

Exec master.dbo. [GetPagRowCount] 'testghost','testtbl',-1

Note: GetPagRowCount is my own proc, but it is convenient to consult the data of each page, and you can also use dbcc page to check it.

Then delete the data of c1a5.

Delete from testtbl where c1o5

Execute select query, the data can no longer be found

Assuming that C1Secret5 deletes data by mistake, we start to do undelete....

Pause Ghost cleanup task

Dbcc traceon (661 mai Mui 1)

Use procedure to check the page information of testtbl. You can see that Ghost record has been generated on clustered pages and index pages. Since there is only one Ghsot record here, we can almost be sure that the location of the deleted data is pageid 78, which is also the target page for undelete. (there may be inaccuracies in this way of positioning. The best way is to obtain the mistakenly deleted PageID through fn_dblog (), and then use this procedure. If the two results are compared, there will be no mistake.)

After the PageID is determined, the next step is to determine the SlotID in which the data is located.

We look at its physical record through dbcc page, and we can see it from record_type. Slot 4 is Ghost record

At this point, we can determine that PageID 78 Magi SlotID 4 is the target of our Undelete.

This is a Clustered table, and its tag is removed by adding the identified bits to the first byte of that Row, so that the tag is Ghost record (unlike heap table).

-

The first Byte of Row, starting with 0, converts the bits 1 to 3 from the right to decimal, and represents the following meaning:

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, use the binary editor to find DB's PageID 78 slot ID 4, and change the Ghost record identification bit of the first byte (decimal 6) to the normal data bit (decimal 0).

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

I checked the page message again and found that it was still marked as Ghost record

Rebuild all index...

Alter index ALL on testtbl rebuild

Check the page information again, it's normal.

However, such a change will cause the record of the system base table to be inconsistent with that of data page, so there will be errors in the execution of dbcc checktable.

The whole process of Clustered Table Undelete must be implemented at last.

Dbcc checktable (testtbl,repair_allow_data_loss)

Repair the data table, although using repair_allow_data_loss, but it will not cause data leakage.

Finally, don't forget to turn off flag 661s.

Dbcc traceoff (661 mai Mui 1)

SQL SERVER Undelete test succeeded ~ ~

The above tests mainly explore the possibility of Undelete, which proves that Undelete is possible. However, there is still a long way to go before it can really be used in the operating environment of OLTP. (the problem of large abnormal momentum and fast positioning of target pageid/slotid and Downtime).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.