The database attached to sql2012 cannot be attached to a database version lower than sql2012

Time:2022-1-5

Yesterday, I just attached the database to sql2012, and then each database did the operation of shrinking the transaction log

The compatibility level has not been changed

An error is reported when it is appended back to SQL2005

In sql2012, it is really 90 level, but it can’t be attached in SQL2005

The same is true for restoring a backup database

The same is true for rebuilding the transaction log

Then I did an experiment to check the difference between the database attached to sql2012 and the database attached to SQL2005

Note: the compatibility level of both databases is 90. I haven’t changed the compatibility level since I attached it to sql2012!!

We use the same database and attach it to SQL2005 and sql2012 respectively to see if the file header of the database has changed after attachment

The compatibility level of this database is 90

After attaching to sql2012, I will not change its compatibility level

The SQL statement for viewing the file header is as follows, which is actually page 0 of the database:

Copy codeThe code is as follows:
DBCC TRACEON(3604,-1)
DBCC PAGE(dlgpos,1,0,3)

It is implemented in sql2012 and SQL2005

Copy and paste the results into a new notepad and name it

Copy codeThe code is as follows:
DBCC execution completed. If DBCC outputs an error message, please contact your system administrator.
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.)[email protected]        
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 [email protected];………….        
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 execution completed. If DBCC outputs an error message, please contact your system administrator.

Sql2012 file header

Copy codeThe code is as follows:
DBCC execution completed. If DBCC outputs an error message, please contact your system administrator.
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  [email protected];.~…….%…..
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.)[email protected];…..
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 execution completed. If DBCC outputs an error message, please contact your system administrator.

Can useBeyond CompareThis software compares the differences between the two file headers

Beyond CompareThis software will mark the same points in the two txt files in blue and the different points in red

When it is attached to sql2012, the file header of the database is out of shape. Even if you have not changed the compatibility level, it also causes the database once attached to sql2012

No more reasons can be attached to SQL2005

You can see that the information recorded in the database of sql2012 is much more detailed than that of SQL2005


You can also use the following SQL statement to see the contents of the file header, but there is less information

1 DBCC fileheader('DLGPOS')


summary

The above experiment proves that when you attach a SQL2005 database to sql2012, sql2012 immediately changes the file header of the database

Even if you do not change the database compatibility level, in fact, the database information has changed (here refers to the file header information)

So you can’t go back to SQL2005

Do not easily attach the database to sql2012 in the future, otherwise……

 

Add something