SQL Server data Recovery preparation TRUNCATE TABLE understanding

When TRUNCATE table occurs, how to recover, I believe most people will choose to restore the backup to TRUNCATE table before, and then re-import the data into the formal table.

So in SQL Server is not really only this method, of course, this is the content of this article will be introduced, the precondition is the SQL Server full recovery model (bulk-logged mode is not tested, not described).

First, learn some of the TRUNCATE table's related knowledge

Official documents:

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

This sentence contains a large amount of information, through the experiment to verify, explained as follows:

TRUNCATE TABLE, the exact deleted values are not recorded in the database log, only the IDs of the pages that truncate the records are logged, and the space occupied by those records is identified as Overridable.

The data in these pages is retained temporarily in the MDF, and the data for the TRUNCATE table is overwritten when new transactions are written to these pages.

Test:

Create a table and insert the data

CREATE TABLE test_truncate (ID int,name varchar (), address varchar) Goinsert into test_truncate Select 1, ' Zhangsan ', ' First road ' Goinsert into test_truncate select 2, ' Wangxiao ', ' second road ' go

Use DBCC IND to find the data page of the table, pagetype=1 as the data page, that is: 288

View data page content using DBCC page

page:  (1:288) buffer:buf @0x000000000563c600bpage = 0x0000000150020000           bhash = 0x0000000000000000           bpageno =  (1:288) bdbid = 9                             breferences = 0                      bcputicks = 0bsamplecount =  0                     bUse1 = 56673                        bstat = 0x10bblog =  0x7adb21cc                   bnext =  0x0000000000000000          page header:page  @0x0000000150020000m_pageId =  (1:288)                    m_headerVersion = 1                  m_type = 1m_ typeflagbits = 0x0                 m_level = 0                          m_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 = 167                     m_reservedCnt = 0                    m_lsn =  (49:7380:2) m_ xactreserved = 0                   m_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 = PRIMARY_RECORD         record attributes =  null_bitmap variable_ columnsrecord size = 36                     memory dump @0x000000006ddf80830000000000000000:    30000800 02000000 03000002 00190024 0077616e  0..............$ .wan0000000000000014:   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 on 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 executing TRUNCATE TABLE

You can see the related records in the data page that still hold the TRUNCATE table.

Then, the second recovery method for truncate table is obvious, extracting the data from the page and reverting it to the table before the data is overwritten.

SQL Server data Recovery preparation TRUNCATE TABLE understanding