昨天我只是将数据库附加到SQL2012，然后各个数据库都做了收缩事务日志的操作

兼容级别这些都没有改

再附加回SQL2005的时候就报错

在SQL2012里附加，确实是90级别，但是在SQL2005死活附加不上

备份数据库再还原也是一样

重建事务日志也是一样

然后我做了一个实验，检查一下附加到SQL2012的数据库和附加到SQL2005的数据库，两个数据库的文件头有什么不同

注意：两个数据库的兼容级别都是90，附加到SQL2012之后我也没有动过兼容级别！！

我们用同一个数据库，分别附加到SQL2005上和SQL2012上，看一下附加之后数据库的文件头有没有改变

这个数据库的兼容级别是90的

附加到SQL2012之后，我也不改变他的兼容级别

查看文件头的SQL语句如下，实际上就是数据库的第0页：

复制代码 代码如下:

DBCC TRACEON(3604,-1)

DBCC PAGE(dlgpos,1,0,3)

在SQL2012里和SQL2005里都执行一下

将结果复制粘贴到一个新建的记事本里，命名好

复制代码 代码如下:

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:0)

BUFFER:

BUF @0x035D7380

bpage = 0x05BC0000 bhash = 0x00000000 bpageno = (1:0)

bdbid = 5 breferences = 0 bUse1 = 8142

bstat = 0xc00009 blog = 0x59ca2159 bnext = 0x00000000

PAGE HEADER:

Page @0x05BC0000

m_pageId = (1:0) m_headerVersion = 1 m_type = 15

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208

m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064

Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99

m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0

m_slotCnt = 1 m_freeCnt = 7636 m_freeData = 2844

m_reservedCnt = 0 m_lsn = (132:328:1) m_xactReserved = 0

m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1431739479

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL

DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED

File Header Data:

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x5D95C952

00000000: 30000800 00000000 2d000000 00000000 ?0.......-.......

00000010: 2c007a00 7a007c00 7e008200 86008a00 ?,.z.z.|.~.......

00000020: 8e009800 a200ac00 ac00b000 b400b800 ?................

00000030: bc00c600 e200ec00 f6000001 10011a01 ?................

00000040: 2a012e01 38013801 44015401 54015401 ?*...8.8.D.T.T.T.

00000050: 54015401 54015401 64016401 64016e01 ?T.T.T.T.d.d.d.n.

00000060: 78019401 9e01ae01 ca019eb2 1d7874c9 ?x............xt.

00000070: 5d4d85b9 d1422e77 c1620100 01008002 ?]M...B.w.b......

00000080: 0000ffff ffff8000 00000000 00000000 ?................

00000090: 00000000 00000000 00000000 00000000 ?................

000000A0: 00000000 00000000 00000000 80010000 ?................

000000B0: 00000000 ffffffff 00020000 7e000000 ?............~...

000000C0: c6000000 01007e00 0000c600 00000100 ?......~.........

000000D0: 0000355a f94bc493 9149ac29 044140d0 ?..5Z.K...I.).A@.

000000E0: 3b1f7e00 0000b100 00002500 00000000 ?;.~.......%.....

000000F0: 00000000 00008400 00003601 00002500 ?..........6...%.

00000100: 0567c9fb b5520346 853c86ad b3f47661 ?.g...R.F.<....va

00000110: 00000000 00000000 0000018e a4cb618f ?..............a.

00000120: 414c90c3 68f1a4fd 0d810800 00007e00 ?AL..h.........~.

00000130: 0000c600 00000100 44004c00 47005000 ?........D.L.G.P.

00000140: 4f005300 cf6c06e9 4b9b3649 a11c2b70 ?O.S..l..K.6I..+p

00000150: dbebb977 355af94b c4939149 ac290441 ?...w5Z.K...I.).A

00000160: 40d03b1f 00000000 00000000 00000000 ?@.;.............

00000170: 00000000 00000000 00000000 00000000 ?................

00000180: 00000000 00000000 00000000 00000000 ?................

00000190: 00000000 7e000000 b1000000 25003804 ?....~.......%.8.

000001A0: 48829a28 104c95f3 4b9d6a91 ab480000 ?H..(.L..K.j..H..

000001B0: 00000000 00000000 00000000 00000000 ?................

000001C0: 00000000 00000000 0000???????????????..........

BindingID = 781db29e-c974-4d5d-85b9-d1422e77c162 FileGroupId = 1

FileIdProp = 1 Size = 640 MaxSize = 65535

Growth = 128 Perf = 0 BackupLsn = (0:0:0)

MaxLsn = (126:198:1) FirstLsn = (126:177:37) OldestRestoredLsn = (0:0:0)

FirstUpdateLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL] CreateLsn = (0:0:0)

DifferentialBaseLsn = (132:310:37) DifferentialBaseGuid = fbc96705-52b5-4603-853c-86adb3f47661

MinSize = 384 Status = 0 UserShrinkSize = 65535

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

SQL2012文件头

复制代码 代码如下:

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:0)

BUFFER:

BUF @0x0456ACA8

bpage = 0x187CA000 bhash = 0x00000000 bpageno = (1:0)

bdbid = 9 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 8145 bstat = 0x9

blog = 0x21215a7a bnext = 0x00000000

PAGE HEADER:

Page @0x187CA000

m_pageId = (1:0) m_headerVersion = 1 m_type = 15

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208

m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064

Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99

m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0

m_slotCnt = 1 m_freeCnt = 7636 m_freeData = 3302

m_reservedCnt = 0 m_lsn = (141:733:159) m_xactReserved = 0

m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 426768658

DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL

DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED

File Header Data:

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 458

Memory Dump @0x1019CB1C

00000000: 30000800 00000000 2d000000 00000000 2c007a00 0.......-.......,.z.

00000014: 7a007c00 7e008200 86008a00 8e009800 a200ac00 z.|.~...............

00000028: ac00b000 b400b800 bc00c600 e200ec00 f6000001 ....................

0000003C: 10011a01 2a012e01 38013801 44015401 54015401 ....*...8.8.D.T.T.T.

00000050: 54015401 54015401 64016401 64016e01 78019401 T.T.T.T.d.d.d.n.x...

00000064: 9e01ae01 ca019eb2 1d7874c9 5d4d85b9 d1422e77 .........xt.]M...B.w

00000078: c1620100 01000003 0000ffff ffff8000 00000000 .b..................

0000008C: 00000000 00000000 00000000 00000000 00000000 ....................

000000A0: 00000000 00000000 00000000 80010000 00000000 ....................

000000B4: ffffffff 00020000 7e000000 c6000000 01007e00 ........~.........~.

000000C8: 0000c600 00000100 0000355a f94bc493 9149ac29 ..........5Z.K...I.)

000000DC: 044140d0 3b1f7e00 0000b100 00002500 00000000 .A@.;.~.......%.....

000000F0: 00000000 00008400 00003601 00002500 0567c9fb ..........6...%..g..

00000104: b5520346 853c86ad b3f47661 00000000 00000000 .R.F.<....va........

00000118: 0000018e a4cb618f 414c90c3 68f1a4fd 0d810800 ......a.AL..h.......

0000012C: 00007e00 0000c600 00000100 44004c00 47005000 ..~.........D.L.G.P.

00000140: 4f005300 cf6c06e9 4b9b3649 a11c2b70 dbebb977 O.S..l..K.6I..+p...w

00000154: 355af94b c4939149 ac290441 40d03b1f 00000000 5Z.K...I.).A@.;.....

00000168: 00000000 00000000 00000000 00000000 00000000 ....................

0000017C: 00000000 00000000 00000000 00000000 00000000 ....................

00000190: 00000000 7e000000 b1000000 25003804 48829a28 ....~.......%.8.H..(

000001A4: 104c95f3 4b9d6a91 ab480000 00000000 00000000 .L..K.j..H..........

000001B8: 00000000 00000000 00000000 00000000 0000 ..................

BindingID = 781db29e-c974-4d5d-85b9-d1422e77c162 FileIdProp = 1

FileGroupId = 1 Size = 768 MaxSize = 65535

Growth = 128 Perf = 0 BackupLsn = (0:0:0)

FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]

MinSize = 384 Status = 0 UserShrinkSize = 65535

SectorSize = 512 MaxLsn = (126:198:1) FirstLsn = (126:177:37)

CreateLsn = (0:0:0) DifferentialBaseLsn = (132:310:37)

DifferentialBaseGuid = fbc96705-52b5-4603-853c-86adb3f47661 FileOfflineLsn = (0:0:0)

FileIdGuid = cba48e01-8f61-4c41-90c3-68f1a4fd0d81 RestoreStatus = 8

RestoreRedoStartLsn = (126:198:1) RestoreSourceGuid = e9066ccf-9b4b-4936-a11c-2b70dbebb977

HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]

FstrContainerSize = [NULL] MaxLsnBranchId = 4bf95a35-93c4-4991-ac29-044140d03b1f

SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]

ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0)

RestoreDifferentialBaseLsn = (126:177:37)

RestoreDifferentialBaseGuid = 82480438-289a-4c10-95f3-4b9d6a91ab48

RestorePathOrigin

hex (dec) = 0x00000000:00000000:0000 (0:0:0)

m_guid = 00000000-0000-0000-0000-000000000000

DatabaseEncryptionFileState = [NULL]FCBFileDEK = [NULL]

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

可以用Beyond Compare这个软件比较一下两者的文件头的差异

Beyond Compare这个软件会把两个txt文件中的相同点用蓝色标记出来，不同点用红色标记出来

当附加到SQL2012之后，数据库的文件头已经走样了，就算你没有动过兼容级别，这也是造成曾经附加到SQL2012的数据库

再也附加不上SQL2005上的原因

可以看到SQL2012的数据库记录的信息比SQL2005详细多了，多了很多内容

也可以用下面的SQL语句看文件头的内容，不过信息比较少

1 DBCC fileheader('DLGPOS')

总结

上面的实验证明了，当你将一个SQL2005的数据库附加到SQL2012上的时候，SQL2012马上改变数据库的文件头

就算你不动数据库兼容级别，实际上数据库的信息已经改变了（这里指文件头信息）

所以你死活附加不回去SQL2005了

以后不要轻易附加数据库到SQL2012上，不然的话。。。。。。

补充一下