附加到SQL2012的数据库就不能再附加到低于SQL2012的数据库版本的解决方法

昨天我只是将数据库附加到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上,不然的话。。。。。。

 

补充一下

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐