How to upgrade sql server express 2005 to 2008R2?

How to upgrade sql server express 2005 to 2008R2

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

• Answer:

  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.