3

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 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.

  • Unfortunately, that isn't the answer. 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. – Laughing Vergil Nov 13 '18 at 19:09
  • 1
    Does this behavior persist SQL 2008 SP4, which is the only currently-supported service pack? – David Browne - Microsoft Nov 15 '18 at 18:13
  • 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. – Laughing Vergil Nov 15 '18 at 18:45
  • 1
    Just curious, did you try putting it on compat 100 before backing up, even though it reports as compat 100? In sys.databases? – Randi Vertongen Nov 15 '18 at 18:58
  • 1
    Yes indeed we did. We also tried toggling it to compat 90, then back to compat 100 before backing up. – Laughing Vergil Nov 16 '18 at 0:38
1

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 os 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. – Laughing Vergil Nov 13 '18 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...) – Tibor Karaszi Nov 13 '18 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. – Laughing Vergil Nov 14 '18 at 1:09
1

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. – Laughing Vergil Nov 21 '18 at 16:45
1

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('<Your_DB_Name>') 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('<Your_DB_Name>',1,9,3)

Where the syntax is

DBCC PAGE('DATABASE_NAME' | DB_ID, <FILE_ID>, <PAGE>, <LEVEL_OF_DETAILS>)

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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