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