附加到SQL2012的數(shù)據(jù)庫就不能再附加到低于SQL2012的數(shù)據(jù)庫版本

昨天我只是將數(shù)據(jù)庫附加到SQL2012，然后各個數(shù)據(jù)庫都做了收縮事務日志的操作

兼容級別這些都沒有改

再附加回SQL2005的時候就報錯

在SQL2012里附加，確實是90級別，但是在SQL2005死活附加不上

備份數(shù)據(jù)庫再還原也是一樣

重建事務日志也是一樣

---

然后我做了一個實驗，檢查一下附加到SQL2012的數(shù)據(jù)庫和附加到SQL2005的數(shù)據(jù)庫，兩個數(shù)據(jù)庫的文件頭有什么不同

注意：兩個數(shù)據(jù)庫的兼容級別都是90，附加到SQL2012之后我也沒有動過兼容級別！！

我們用同一個數(shù)據(jù)庫，分別附加到SQL2005上和SQL2012上，看一下附加之后數(shù)據(jù)庫的文件頭有沒有改變

這個數(shù)據(jù)庫的兼容級別是90的

附加到SQL2012之后，我也不改變他的兼容級別

查看文件頭的SQL語句如下，實際上就是數(shù)據(jù)庫的第0頁：

1 DBCC TRACEON(3604,-1) 2 DBCC PAGE(dlgpos,1,0,3)

在SQL2012里和SQL2005里都執(zhí)行一下

將結果復制粘貼到一個新建的記事本里，命名好

SQL2005文件頭

1 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。 2 3 PAGE: (1:0) 4 5 6 BUFFER: 7 8 9 BUF @0x035D7380 10 11 bpage = 0x05BC0000 bhash = 0x00000000 bpageno = (1:0) 12 bdbid = 5 breferences = 0 bUse1 = 8142 13 bstat = 0xc00009 blog = 0x59ca2159 bnext = 0x00000000 14 15 PAGE HEADER: 16 17 18 Page @0x05BC0000 19 20 m_pageId = (1:0) m_headerVersion = 1 m_type = 15 21 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208 22 m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064 23 Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99 24 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0 25 m_slotCnt = 1 m_freeCnt = 7636 m_freeData = 2844 26 m_reservedCnt = 0 m_lsn = (132:328:1) m_xactReserved = 0 27 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1431739479 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL 32 DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED 33 34 File Header Data: 35 36 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 37 38 Memory Dump @0x5D95C952 39 40 00000000: 30000800 00000000 2d000000 00000000 ?0.......-....... 41 00000010: 2c007a00 7a007c00 7e008200 86008a00 ?,.z.z.|.~....... 42 00000020: 8e009800 a200ac00 ac00b000 b400b800 ?................ 43 00000030: bc00c600 e200ec00 f6000001 10011a01 ?................ 44 00000040: 2a012e01 38013801 44015401 54015401 ?*...8.8.D.T.T.T. 45 00000050: 54015401 54015401 64016401 64016e01 ?T.T.T.T.d.d.d.n. 46 00000060: 78019401 9e01ae01 ca019eb2 1d7874c9 ?x............xt. 47 00000070: 5d4d85b9 d1422e77 c1620100 01008002 ?]M...B.w.b...... 48 00000080: 0000ffff ffff8000 00000000 00000000 ?................ 49 00000090: 00000000 00000000 00000000 00000000 ?................ 50 000000A0: 00000000 00000000 00000000 80010000 ?................ 51 000000B0: 00000000 ffffffff 00020000 7e000000 ?............~... 52 000000C0: c6000000 01007e00 0000c600 00000100 ?......~......... 53 000000D0: 0000355a f94bc493 9149ac29 044140d0 ?..5Z.K...I.).A@. 54 000000E0: 3b1f7e00 0000b100 00002500 00000000 ?;.~.......%..... 55 000000F0: 00000000 00008400 00003601 00002500 ?..........6...%. 56 00000100: 0567c9fb b5520346 853c86ad b3f47661 ?.g...R.F.<....va 57 00000110: 00000000 00000000 0000018e a4cb618f ?..............a. 58 00000120: 414c90c3 68f1a4fd 0d810800 00007e00 ?AL..h.........~. 59 00000130: 0000c600 00000100 44004c00 47005000 ?........D.L.G.P. 60 00000140: 4f005300 cf6c06e9 4b9b3649 a11c2b70 ?O.S..l..K.6I..+p 61 00000150: dbebb977 355af94b c4939149 ac290441 ?...w5Z.K...I.).A 62 00000160: 40d03b1f 00000000 00000000 00000000 ?@.;............. 63 00000170: 00000000 00000000 00000000 00000000 ?................ 64 00000180: 00000000 00000000 00000000 00000000 ?................ 65 00000190: 00000000 7e000000 b1000000 25003804 ?....~.......%.8. 66 000001A0: 48829a28 104c95f3 4b9d6a91 ab480000 ?H..(.L..K.j..H.. 67 000001B0: 00000000 00000000 00000000 00000000 ?................ 68 000001C0: 00000000 00000000 0000???????????????.......... 69 BindingID = 781db29e-c974-4d5d-85b9-d1422e77c162 FileGroupId = 1 70 FileIdProp = 1 Size = 640 MaxSize = 65535 71 Growth = 128 Perf = 0 BackupLsn = (0:0:0) 72 MaxLsn = (126:198:1) FirstLsn = (126:177:37) OldestRestoredLsn = (0:0:0) 73 FirstUpdateLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL] CreateLsn = (0:0:0) 74 DifferentialBaseLsn = (132:310:37) DifferentialBaseGuid = fbc96705-52b5-4603-853c-86adb3f47661 75 MinSize = 384 Status = 0 UserShrinkSize = 65535 76 77 78 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。

SQL2012文件頭

1 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。 2 3 PAGE: (1:0) 4 5 6 BUFFER: 7 8 9 BUF @0x0456ACA8 10 11 bpage = 0x187CA000 bhash = 0x00000000 bpageno = (1:0) 12 bdbid = 9 breferences = 0 bcputicks = 0 13 bsampleCount = 0 bUse1 = 8145 bstat = 0x9 14 blog = 0x21215a7a bnext = 0x00000000 15 16 PAGE HEADER: 17 18 19 Page @0x187CA000 20 21 m_pageId = (1:0) m_headerVersion = 1 m_type = 15 22 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208 23 m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064 24 Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99 25 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0 26 m_slotCnt = 1 m_freeCnt = 7636 m_freeData = 3302 27 m_reservedCnt = 0 m_lsn = (141:733:159) m_xactReserved = 0 28 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 426768658 29 DB Frag ID = 1 30 31 Allocation Status 32 33 GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL 34 DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED 35 36 File Header Data: 37 38 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 39 Record Size = 458 40 Memory Dump @0x1019CB1C 41 42 00000000: 30000800 00000000 2d000000 00000000 2c007a00 0.......-.......,.z. 43 00000014: 7a007c00 7e008200 86008a00 8e009800 a200ac00 z.|.~............... 44 00000028: ac00b000 b400b800 bc00c600 e200ec00 f6000001 .................... 45 0000003C: 10011a01 2a012e01 38013801 44015401 54015401 ....*...8.8.D.T.T.T. 46 00000050: 54015401 54015401 64016401 64016e01 78019401 T.T.T.T.d.d.d.n.x... 47 00000064: 9e01ae01 ca019eb2 1d7874c9 5d4d85b9 d1422e77 .........xt.]M...B.w 48 00000078: c1620100 01000003 0000ffff ffff8000 00000000 .b.................. 49 0000008C: 00000000 00000000 00000000 00000000 00000000 .................... 50 000000A0: 00000000 00000000 00000000 80010000 00000000 .................... 51 000000B4: ffffffff 00020000 7e000000 c6000000 01007e00 ........~.........~. 52 000000C8: 0000c600 00000100 0000355a f94bc493 9149ac29 ..........5Z.K...I.) 53 000000DC: 044140d0 3b1f7e00 0000b100 00002500 00000000 .A@.;.~.......%..... 54 000000F0: 00000000 00008400 00003601 00002500 0567c9fb ..........6...%..g.. 55 00000104: b5520346 853c86ad b3f47661 00000000 00000000 .R.F.<....va........ 56 00000118: 0000018e a4cb618f 414c90c3 68f1a4fd 0d810800 ......a.AL..h....... 57 0000012C: 00007e00 0000c600 00000100 44004c00 47005000 ..~.........D.L.G.P. 58 00000140: 4f005300 cf6c06e9 4b9b3649 a11c2b70 dbebb977 O.S..l..K.6I..+p...w 59 00000154: 355af94b c4939149 ac290441 40d03b1f 00000000 5Z.K...I.).A@.;..... 60 00000168: 00000000 00000000 00000000 00000000 00000000 .................... 61 0000017C: 00000000 00000000 00000000 00000000 00000000 .................... 62 00000190: 00000000 7e000000 b1000000 25003804 48829a28 ....~.......%.8.H..( 63 000001A4: 104c95f3 4b9d6a91 ab480000 00000000 00000000 .L..K.j..H.......... 64 000001B8: 00000000 00000000 00000000 00000000 0000 .................. 65 BindingID = 781db29e-c974-4d5d-85b9-d1422e77c162 FileIdProp = 1 66 FileGroupId = 1 Size = 768 MaxSize = 65535 67 Growth = 128 Perf = 0 BackupLsn = (0:0:0) 68 FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL] 69 MinSize = 384 Status = 0 UserShrinkSize = 65535 70 SectorSize = 512 MaxLsn = (126:198:1) FirstLsn = (126:177:37) 71 CreateLsn = (0:0:0) DifferentialBaseLsn = (132:310:37) 72 DifferentialBaseGuid = fbc96705-52b5-4603-853c-86adb3f47661 FileOfflineLsn = (0:0:0) 73 FileIdGuid = cba48e01-8f61-4c41-90c3-68f1a4fd0d81 RestoreStatus = 8 74 RestoreRedoStartLsn = (126:198:1) RestoreSourceGuid = e9066ccf-9b4b-4936-a11c-2b70dbebb977 75 HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL] 76 FstrContainerSize = [NULL] MaxLsnBranchId = 4bf95a35-93c4-4991-ac29-044140d03b1f 77 SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL] 78 ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) 79 RestoreDifferentialBaseLsn = (126:177:37) 80 RestoreDifferentialBaseGuid = 82480438-289a-4c10-95f3-4b9d6a91ab48 81 RestorePathOrigin 82 83 hex (dec) = 0x00000000:00000000:0000 (0:0:0) 84 m_guid = 00000000-0000-0000-0000-000000000000 85 DatabaseEncryptionFileState = [NULL]FCBFileDEK = [NULL] 86 87 88 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。

可以用Beyond Compare這個軟件比較一下兩者的文件頭的差異

Beyond Compare這個軟件會把兩個txt文件中的相同點用藍色標記出來，不同點用紅色標記出來

當附加到SQL2012之后，數(shù)據(jù)庫的文件頭已經(jīng)走樣了，就算你沒有動過兼容級別，這也是造成曾經(jīng)附加到SQL2012的數(shù)據(jù)庫

再也附加不上SQL2005上的原因

可以看到SQL2012的數(shù)據(jù)庫記錄的信息比SQL2005詳細多了，多了很多內(nèi)容

---

也可以用下面的SQL語句看文件頭的內(nèi)容，不過信息比較少

1 DBCC fileheader('DLGPOS')

---

總結

上面的實驗證明了，當你將一個SQL2005的數(shù)據(jù)庫附加到SQL2012上的時候，SQL2012馬上改變數(shù)據(jù)庫的文件頭

就算你不動數(shù)據(jù)庫兼容級別，實際上數(shù)據(jù)庫的信息已經(jīng)改變了（這里指文件頭信息）

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

以后不要輕易附加數(shù)據(jù)庫到SQL2012上，不然的話。。。。。。

?

補充一下

http://www.cnblogs.com/lyhabc/archive/2013/01/20/2869057.html

?

如有不對的地方，歡迎大家拍磚o(∩_∩)o

總結

以上是生活随笔為你收集整理的附加到SQL2012的数据库就不能再附加到低于SQL2012的数据库版本的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。