If I have a SQL Server 2005 database, and want to upgrade it to run on 2008, what is the best approach, and what problems am I likely to encounter?
The first thing you should do is download and run the Microsoft SQL Server 2008 Upgrade Advisor. Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008:
Upgrade Advisor does not change any configuration settings or data.
Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the report server.
Upgrade Advisor provides the UpgradeAdvisorWizardCmd command-prompt utility to help you more easily scan multiple instances of SQL Server. See the Upgrade Advisor documentation for additional information.
Uninstalling Upgrade Advisor requires the .NET Framework 2.0. Uninstall Upgrade Advisor before uninstalling the .NET Framework 2.0.
SQL Server 2008 Books Online: Using Upgrade Advisor to Prepare for Upgrades
Problems you might encounter:
Besides following the good advice provided by Mitch Wheat, when I upgrade between versions of SQL Server, I prefer to upgrade hardware at the same time. This way, I can install the new hardware, the new OS, and the new SQL Server version, and verify they are all working. Then, I perform a trial migration of the databases onto the new hardware and software, and then I can test the migration without affecting my existing installation. Once I am convinced the test migration was successful, I then make the actual cut over during an appropriate maintenance period. If there should be any problems with the cut over, I can always revert back to the older installation. When upgrading, I always prefer to have a back out plan ready, just in case.
No one has followed this question yet.