I currently have a Server 2003 R2 VM running SQL 2005 Standard. The requirements for Standard have gone away and we can suffice with Express. We also have a requirement to upgrade it to SQL 2008 R2. When trying to upgrade, the SQL installer bombs out claiming it's not a supported upgrade path (which it isn't). That being said, what's the easiest way to convert from SQL 2005 Std to 2008R2 Express? Do I need to uninstall 2005, install 2008R2 and then attach the databases and re-configure security?
3 Answers
Personally, rather than trying to detach/reattach the databases, I would run full backups of everything, then restore them to the new installation.
On several occasions when dealing with odd, "sideways" upgrades like this, I've experienced badness when trying to re-attach; backup/restore has been much more reliable for me.
-
All were good answers, but I chose your because of the recommendation of the db backup as well as detach/attach, just to be safe. I ran the "upgrade" yesterday evening and this morning and all seems to be working well. I took backups as well as detach, but the re-attach worked fine (and was less work in the long run).– DanDec 8, 2010 at 13:06
That is pretty much the option. That or you can uninstall Standard, install SQL 2005, restore the system databases then upgrade from SQL 2005 to SQL 2008 R2.
Its pretty simple, Take backup of all your databases, I suggest take backup of .mdf and .ldf files instead of .bak files.
Uninstall SQL 2005 Standard Edition and all its components. Install Fresh SQL 2005 Express Edition or Enterprise Edition.
Attach all your .mdf files and databases will be restored.
Then upgrade your SQL 2005 to SQL 2008 R2. Refer above link that will help you to upgrade SQL.
