I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the GUI or T-SQL. If you view the options in the GUI (Right Click on DB, Select Properties, Select "Options" and then look at the "Compatibility level:", it shows options for only past versions 7(70), 2000 (80), and 2005 (90).
This is in production and I cannot simply drop and recreate it as it is constantly in use. When I backed up and restored it to another SQL Server instance (2014) all compatibility levels were available, but that's not an option without taking down all VM's that are tied to it. If all else fails, yes that is an option, but one that brings no joy.
When I run :
ALTER DATABASE [VIM_VCDB] SET COMPATIBILITY_LEVEL = 100
I get the following:
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '100'.
This is true for all system DB's on this box also... How to I raise the compatibility level for this? Is it a bad install of the SQL Server 2008 Express?