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:

    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)
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 data type, which is what it should be returned as in a SQL Server 2008/R2 database.

In my last tests, I added a test table to the DB containing a date and a time field, and inserted a row of data. Then I backed up and restored the DB again. The table worked correctly, though the compatibility level was 90. I also created a table with date, time, and datetime2 fields with no problems.

I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.

  • I created a backup to a new backup file, then restored from that new backup file (with only one backup in it) and it still did the same thing. Also, this was discovered because of month-end backups that get restored as an EOM database. If the data wasn't current, we would hear loud yelling from the reporting people. Nov 13, 2018 at 19:09
  • 2008 SP3, SP4, 2012 have all been checked, and the problem persists. However, see below for a workaround answer we finally came up with. It doesn't answer what the problem is/was, but it makes it possible to solve the issue. Nov 15, 2018 at 18:45

3 Answers 3


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.


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


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.


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

DBCC PAGE('<Your_DB_Name>',1,9,3)

Where the syntax is


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:


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?


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.


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

We have discovered a workaround which will provide an effective answer, although it goes nowhere to answering the WHY of the problem.

Problem TL;DR

  1. Backup Database (Compatibility Level 100, on SQL Server 2008 server)
  2. Restore Database (To any server - 2008, 20012, including same server backed up on.)
  3. Check Compatibility Level, shows as Compatibility Level 90 (e.g. SQL 2005)

Solution TL;DR

  1. Backup Database
  2. Restore Database to SQL Server 2008 server (New name on same server)
  3. Set Compatibility Level of restored DB to 100 (SQL 2008)
  4. Backup recently restored DB
  5. Restore new backup from new DB
  6. Voila - Restored DB is in Compatibility Level 100

Notes: We tried resetting the compatibility level on the original DBs, and this had no effect - Reset to 2005 (90), exit, re-enter, reset to 2008, exit, re-enter, backup, restore. Restored DB comes in as compatibility 90. Hair pulling commences.

  • 1
    Update: Additional analysis of the databases on the server shows that all DBs that were migrated from SQL 2005 to this 2008 DB in the original migration (years ago) have this issue. I wrote a bit of analytical code to check all of the SQL Servers to see where their version did not match the server version, and there appear to be several other older servers with this issue. It specifically appears to have been a problem when a DB was migrated, and this issue may have been fixed years ago. We'll be aware for future migrations, though. Nov 21, 2018 at 16:45

First: the upgrade done when you restore handles the physical upgrade of the database. A database's physical version is always the same version as the instance version you run it in. I.e., restore from a prior version and it gets upgraded. And you cannot restore from a more recent version. The physical version is not to be confused with the compatibility level.

The compat level follows with the backup. Backup a database with compat level, say, 90. Then, regardless of the version of SQL Server where you produced that backup, it will restore with compat level 90. You can of course after the restore change compat level both up and down (withing obvious limitations) as much as you want.

  • The compatibility level of the source DB is 100. After a backup and restore, the compatibility level of the restored DB is 90. I noted the upgrade process to clarify that I had indeed performed a restore to a SQL Server 2012 instance. Nov 13, 2018 at 19:29
  • 1
    Ah, so you have a database on a 10.0 (2008) server, and you are 100% certain that this database is in compat level 100. And then you restore it into a a 11.0 (2012) server, And after that restore the compat level for that database was lowered (is that a word?) to 90. Is that correct? If so, can you repro this with other databases? What compat level does RESTORE HEADERONLY say that this database has in the backup? (I'm looking for info so I perhaps can repro what you are seeing...) Nov 13, 2018 at 20:02
  • This can be reproed with four of the 20 or so DBs on that server, all related. Also, when restoring to a new name on the same server it is backed up from (so SQL 2008), it ALSO restores as SQL 2005 (compatibility level 90). Honestly, bizarre. Nov 14, 2018 at 1:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.