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?
Convert/Upgrade SQL 2005 Standard to 2008R2 Express
sql-server-2005sql-server-2008-r2
Related Solutions
It should be simple.
please relax for some time and read the below links once. and start again.
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-installation.html
http://technet.microsoft.com/en-us/library/ms144278(SQL.90).aspx
SQL Server 2005 is supported on Windows 7 in the same manner as Windows Vista, but SQL Server 2005 SP3 is required.
You can use the "upgrade in place" feature of SQL Server 2005 to SQL Server 2008. IIRC, you can also use the Windows Server 2008R2 installer to upgrade from Windows Server 2003 because Windows Server 2003 is only two versions back.
This answer focuses on SQL Server. I recommend that you do a couple of things:
- Read Microsoft's SQL Server 2008 Upgrade Technical Reference Guide. It is long, nearly 500 pages, but you can skim/skip some sections like those pertaining to side-by-side upgrades. There is a section that describes the costs and benefits of "upgrade in place" and "side by side upgrade".
- Be sure that all of your databases are supported on the versions of Windows and SQL Server that you intend to use. In the case of third-party software, that means you should ask your vendors. You may find that you need to upgrade to more recent versions of the third party software for that software to work properly with SQL Server 2008. With your own code, it is best to thoroughly test your software on a server with the new versions of Windows Server and SQL Server before you do the upgrade "for real". At the very least, run the SQL Server 2008 Upgrade Wizard, which will point out common problems.
- After the upgrade, have some sort of quick 'smoke test' that will convince your users that their software works OK. Once users start modifying data, it will be increasing difficult to go back to the old versions of stuff because you will not be able to easily move their changes backwards.
- If a problem is discovered a few days into running the new systems, you may be stuck with it because going backwards would throw too much information away or you would have to spend days or weeks finding a way to migrate your data backwards, and the situation is worse if some things work and some things don't work. Some problems might have workarounds and just aren't worth the trouble. I like to have a list of "reasons we will roll back the upgrade" agreed upon by all stakeholders before starting. I also like to have a drop-dead time for any talk of rolling back the upgrade. That might sound like "After lunchtime on the first day, we are stuck with whatever we have".
- After you upgrade to SQL Server 2008, any other SQL Server 2005 computers that you have will no longer be able to read backup files. This is important if you restore production backups to development or test machines or trade backups with a third party. There is no way to create a "2005-compatible" backup from a 2008 server. SQL Server 2008 can read older backups, so any backups that you have already taken can be read by the new SQL Server.Many developers are surprised by this.
- With in-place upgrades, you cannot go backwards, doubly so if you are replacing the OS. If you can, isolate the machine from user changes and image the drive with Ghost or something similar before doing the upgrade. If something catastrophic happens, you should be able to get back to where you were before the upgrade. Half of being a good admin is knowing what to do if something catastrophic happens, because it does with surprising frequency.
- You should make sure that you upgrade to the latest service packs for Windows Server and SQL Server while you have the server offline.
- Make sure that your hardware is compatible with Windows Server 2008 R2. That means having drivers downloaded and ready to go for any unusual hardware: RAID cards, fancy NICs, FibreChannel HBAs, etc.
Best Answer
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.