SQL Server資料恢複準備之TRUNCATE TABLE理解

標籤：匯入   use   hang   分享圖片   dmi   lag   ref   page   tool   

當truncate table發生時，如何進行恢複，相信大部分人都會選擇通過還原備份到truncate table前，然後將資料重新匯入正式表中。

那麼在SQL Server中是不是真的只有這種方法呢，當然不是，這也是本文即將介紹的內容，前提條件是SQL Server完整復原模式(大容量記錄模式未做測試，暫不介紹)。

首先，瞭解一下truncate table的一些相關知識

官檔：

TRUNCATE TABLE 通過釋放用於儲存表資料的資料頁刪除資料，且僅在交易記錄中記錄頁釋放。

這句話包含的資訊量是很大的，通過實驗進行驗證，解釋如下：

truncate table時，資料庫日誌中不會記錄確切的已刪除值，只記錄截斷記錄的頁的ID，並且這些記錄所佔用的空間會被標識為可重寫，

mdf中會暫時保留這些頁內的資料，當有新事務寫入這些頁時，truncate table的資料將會被覆蓋。

測試：

建立一張表，並插入資料

create table test_truncate(id int,name varchar(20),address varchar(20))goinsert into test_truncate select 1,'zhangsan','first road'goinsert into test_truncate select 2,'wangxiao','second road'go

利用dbcc ind找到該表的資料頁，如下PageType=1為資料頁，即為：288

使用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.

可以看到資料頁中存在的記錄

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

執行truncate table後

可以看到資料頁中仍保留著truncate table的相關記錄。

那麼，truncate table的第二種恢複方法就顯而易見了，在這些資料被覆蓋之前，從頁面中將資料提取出來，並恢複到表中。

SQL Server資料恢複準備之TRUNCATE TABLE理解