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

When truncate table occurs, it is believed that most people will choose to restore the backup to truncate table, and then import the data back into the official table.

So is this really the only way in SQL Server? of course not, this is also what this article will introduce. The prerequisite is the full recovery model of SQL Server (the bulk log model has not been tested and will not be introduced for the time being).

First of all, learn some knowledge about truncate table

Official file:

TRUNCATE TABLE deletes the data by releasing the data page used to store the table data, and only the page release is recorded in the transaction log.

This sentence contains a large amount of information, which is verified by experiments and explained as follows:

When truncate table, the exact deleted value is not recorded in the database log, only the ID of the truncated page is recorded, and the space occupied by these records is identified as rewritable

The data in these pages is temporarily retained in mdf, and when a new transaction is written to these pages, truncate table's data is overwritten (the data page is format and then reused).

Test:

Create a table and insert data

Create table test_truncate (id int,name varchar (20), address varchar (20)) goinsert into test_truncate select 1, the first road'goinsert into test_truncate select, the second road'go.

Use dbcc ind to find the data page of the table. The following PageType=1 is the data page, namely: 288

Use dbcc page to view the contents of the data page

PAGE: (1PAGE 288) BUFFER:BUF @ 0x000000000563C600bpage = 0x0000000150020000 bhash = 0x0000000000000000 bpageno = (1VO288) bdbid = 9 breferences = 0 bcputicks = 0bsampleCount = 0 bUse1 = 56673 bstat = 0x10bblog = 0x7adb21cc bnext = 0x0000000000000000 PAGE HEADER:Page @ 0x0000000150020000m_pageId = (10x0 m_level 288) m_headerVersion = 1m_type = 1m_typeFlagBits = 0x0 m_level = 0m _ flagBits = 0x8000m_objId (AllocUnitId.idObj) = 489 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594069975040 Metadata: PartitionId = 72057594062241792 Metadata: IndexId = 0Metadata: ObjectId = 935674381 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8 m_slotCnt = 2 m_freeCnt = 8021m_freeData = 167m_reservedCnt = 0 m_lsn = (49 0Metadata 7380) m_xactReserved = 0m _ xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 0 DB Frag ID = 1 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 35Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 35 Memory Dump @ 0x000000006DDF80600000000000000000: 30000800 01000000 03000002 00190023 007a6861 0.#.zha0000000000000014: 6e677361 6e666972 73742072 6f6164 ngsanfirst roadSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x11 Length 8 Length (physical) 8name = zhangsan Slot 0 Column 3 Offset 0x19 Length 10 Length (physical) 10address = first road Slot 1 Offset 0x83 Length 36Record Type = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 36 Memory Dump @ 0x000000006DDF80830000000000000000: 30000800 02000000 03000002 00190024 0077616e 0.$ .wan00000000000014: 67786961 6f736563 6f6e6420 726f6164 gxiaosecond roadSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x11 Length 8 Length (physical) 8name = wangxiao Slot 1 Column 3 Offset 0x19 Length 11 Length (physical) 11address = second road DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can see the records that exist in the data page

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x11 Length 8 Length (physical) 8name = zhangsan Slot 0 Column 3 Offset 0x19 Length 10 Length (physical) 10address = first road Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x11 Length 8 Length (physical) 8name = wangxiao Slot 1 Column 3 Offset 0x19 Length 11 Length (physical) 11address = second road

After performing the truncate table

You can see that the relevant records of truncate table are still kept in the data page.

Then it is obvious that truncate table's second recovery method is to extract the data from the page and restore it to the table before it is overwritten.

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.