当truncate table发生时，如何进行恢复，相信大部分人都会选择通过还原备份到truncate table前，然后将数据重新导入正式表中。那么在SQL Server中是不是真的只有这种方法呢，
当truncate table发生时，如何进行恢复，相信大部分人都会选择通过还原备份到truncate table前，然后将数据重新导入正式表中。
那么在SQL Server中是不是真的只有这种方法呢，当然不是，这也是本文即将介绍的内容，前提条件是SQL Server完整恢复模式(大容量日志模式未做测试，暂不介绍)。
首先，了解一下truncate table的一些相关知识
官档：
TRUNCATE TABLE 通过释放用于存储表数据的数据页删除数据，且仅在事务日志中记录页释放。
这句话包含的信息量是很大的，通过实验进行验证，解释如下：
truncate table时，数据库日志中不会记录确切的已删除值，只记录截断记录的页的ID，并且这些记录所占用的空间会被标识为可重写，
mdf中会暂时保留这些页内的数据，当有新事务写入这些页时，truncate table的数据将会被覆盖(数据页被format，然后重新使用)。
测试：
创建一张表，并插入数据
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的第二种恢复方法就显而易见了，在这些数据被覆盖之前，从页面中将数据提取出来，并恢复到表中。
