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