6

Im using EF 4.1 in visual studio 2010, and I would like my sql server db on the server to be 2008R2 (because I want to use the migration tool from MS for EF in VS).

Tool : http://visualstudiogallery.msdn.microsoft.com/df3541c3-d833-4b65-b942-989e7ec74c87/

Problem is, the migration tool complains the version of the db is too old (90) .. so I figured for 2008R2 it wants it to be 100.

enter image description here

I have installed (somehow) sql server 2005, 2008, 2008r2 on the DB machine. So why does it not create as a 2008r2 database?

I tried changing the compatibility level myself but its limited to 90, 80 and 70, and does not show 100.

Then I installed sql server 2012 on the machine hoping it would upgrade the dbs and allow me to change to 100 but still no.

So how can I convert the dbs below to 100 and change the server core to use 2008r2 or 2012?

enter image description here

MY SOLUTION :

Detach all DBS from "SqlExpress" instance. And save to desktop. Uninstall Sql Server 2005 - Instance "SqlExpress" from add/remove programs. Move the folders MSSQL.1 / MSSQL.2 to desktop (if these contained ur 2005 data -- you may need to close the sqlserver processes) Run 2008R2 express setup, new install. Create "SqlExpress" instance. Specify sa password. Run MSSMS and connect to SqlExpress instance. Move all dbs back into data dir, and re-attach.

sprocket12
  • 223
  • 2
  • 5
  • You're running the wrong conversion wizard. Read the error message more carefully; it says that the source SQL**100** can not be converted to the destination SQL**90**. You're trying to go down in versions, not up. That's also why 100 is not in the list of compatibility levels; it's not *future* compatible, only backward. (And this is off-topic here.) – Ken White Apr 19 '12 at 12:42
  • Hi ken, I don't think you understood. I did say my DB was 2005, so the VS was expecting 2008 which was not the case so it said it cannot downgrade. Thats why Im asking how can I make my DB 2008 also. To sum up, Im trying to go up versions IN SQL MANAGEMENT STUDIO, and its not showing 100. The WIZARD wants it to be 2008 so it fails on down conversion. I hope that makes sense, –  Apr 19 '12 at 13:50

1 Answers1

3

I would definitely do some cleaning here, instead of upgrading I would remove all flavors of SQL Server and install the engine you want, say 2008R2. It could be the case that you have named instances running a different version than your SQLEXPRESS instance.

Once you install your engine 2008R2, just attach your old 2008 or 2005 database and the upgrade will occur automatically. However, as @ken White suggested, keep in mind that once your database goes with more modern versions you can't go back with older versions.

Ulises
  • 146
  • 3
  • Hi is there any place I can open to see what version the SQLEXPRESS instance is using? And change it from there to use a diff version? Also once I update one db to 2008 can I not set the compatibility of another to 2005 within management studio, or will it upgrade automatically once I attach and not let me set a lower compat level? –  Apr 19 '12 at 15:17
  • Once you upgrade I believe you may still set a compatibility mode for a previous version of SQL Server; however, your database will still be upgraded. This means that you won't be able to attach it with a 2005 engine. – Ulises Apr 20 '12 at 04:29
  • Hi Ulises, so I have the 2008R2 engine installed, how do I get the sqlexpress instance to use that engine? At the moment its using 2005 and I cant see an option anywhere to change it. – sprocket12 Apr 20 '12 at 09:03
  • @MuhammadA: SQLExpress is a named instance. You can have only one. Although you may have several "SQL Server Express" instances. At this point, you need to figure out what version the named instance SQLExpress is running (2005,2008,2008R2 or 2012). You can do that by executing "select @@version". In your situation, if it's anything other than 2008R2 uninstall it and then install 2008R2 – Ulises Apr 20 '12 at 12:25
  • I did the code, its running 2005. So is there no way I can change the current instance of SqlExpress to point to 2008R2 instead? Because its already installed. Or do I have to uninstall 2005 first, and then re-install 2008R2 over itself? I ask this because its a production server and it would probably want restarts. – sprocket12 Apr 20 '12 at 14:22
  • I was suggesting to uninstall just to make it easier, I thought you were on your development machine. Now that it is clearer what you need to do you can just run the 2008R2 installer and select upgrade. Then pick the SQLExpress instance. I've ran into a few instances in which for whatever reason the instance cannot be upgraded. In that case I uninstall 2005 (no rebooting required) – Ulises Apr 20 '12 at 14:25
  • One of the reasons I posted this question is that when I installed 2012 a couple of days ago I chose the upgrade option... but it didnt update anything, well atleast nothing visible to me. But I guess Ill never know unless I also install 2008R2 in upgrade mode. – sprocket12 Apr 20 '12 at 14:33
  • I've ran into that before, that's when I uninstall and make sure I'm in control of what I have in the box – Ulises Apr 20 '12 at 14:38