Odtnhj

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 else unusual happened.

However, when testing the compatibility level on SQL Server 2012, using the same code as above, this is the value returned:

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.

Finally, although the DB reports as SQL Servre 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.

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.