Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.

When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:

MyDB_Name               100 SQL Server 2008/R2
MyDB_Name_MirrorTables  100 SQL Server 2008/R2
MyDB_Name_Reporting     100 SQL Server 2008/R2

As output from this query:

select 
    name, compatibility_level , version_name = 
    case compatibility_level
        when 90  then 'SQL Server 2005'
        when 100 then 'SQL Server 2008/R2'
        when 110 then 'SQL Server 2012'
        else 'unknown - ' + convert(varchar(10), compatibility_level)
    end
from sys.databases

Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.

To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing unusual happened.

However, when looking at the compatibility level on SQL Server 2012, using the same code as above, the information showed up as:

bwh_MyDB_Name_MirrorTables  90  SQL Server 2005

Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version, even though it was backed up on the same server as compatibility level 100.

Finally, although the DB reports as SQL Server 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date dat

DISCLAIMER

This information is being supplied as is. It might assist you in finding the root issue.

While I don't have a solution why your database is converting back to a certain level during a restore, I might be able to shed some light on why the restore might think it has to reset the compatibility level.

DBCC DBINFO

You might want to have a look at the DBCC DBINFO command.

DBCC DBINFO('') WITH TABLERESULTS

Have a look at the following lines:

ParentObject      | Object                     | Field             | VALUE
------------------+----------------------------+-------------------+--------
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_version       |  782
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_createVersion |  705
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_cmptlevel     |  110

While your database might look like it is at a certain level, internally it might be still fostering a lower level, which might be the cause of the restore setting a lower compatibility level.

DBCC PAGE

Instead of using DBCC DBINFO you can use the equally undocumented DBCC PAGE command:

DBCC TRACEON(3604)
DBCC PAGE('',1,9,3)

Where the syntax is

DBCC PAGE('DATABASE_NAME' | DB_ID, , , )

Scroll down a bit and you will find:

...
dbi_version = 782                   dbi_createVersion = 705             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)                      
dbi_dbbackupLSN = 96:512:1 (0x00000060:00000200:0001)                    
dbi_LastLogBackupTime = 2018-11-22 09:15:01.473                          
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2012-03-14 13:14:18.510dbi_dbname = AdventureWorks2012     dbi_dbid = 9
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 10000

Verifying The Database Backup Version

Now for some reason your system might be creating a lower level backup. You can verify your backup with the following command:

RESTORE HEADERONLY FROM DISK='B:\BACKUP\DATABASE_BACKUP.bak'

Which will return the Database Version of your backup file:

DatabaseVersion | CompatibilityLevel | SoftwareVersionMajor | SoftwareVersionMinor | SoftwareVersionBuild
----------------+--------------------+----------------------+----------------------+----------------------
782 | 110 | 12 | 0 | 5590

Information Output has been shortened

Is the database backup the same level as the SQL Server version running?

Questions to Ask Yourself

• Are we using a third-party backup tool?

• Are we using a different server to create the backups?

• Are we using some other tool which might interfere with the backups?

Conclusion

You might have just hit a bug with the version of SQL Server you are using and an database internal issue with the dbi_version and dbi_createVersion information, which causes the compatibility level to set lower than you expect.

-OR-

You might be having issues with the database backup files being created with a down-level compatibility level, because...

• of some 3-rd party issue

• of the dbi_version and/or dbi_createVersion being different in the backup, because of, ....

• a bug

• a 3-rd party issue

StackExchange Database Administrators Q#222389, answer score: 2