Home / server / Questions / 1841
Accepted
dance2die
Asked: 2009-05-01 14:59:50 +0800 CST

How to migrate from SQL Server 2005 to 2008

  • 772

How do you migrate SQL Server 2005 databases to SQL Server 2008?

Is it as easy as backing up 2005 databases and simply restore in 2008? Are there any compatibility issues? Is SQL Server Reporting service changed in anyway?

How would you go about upgrading SQL server?

sql-server sql-server-2005 migration sql-server-2008

6 Answers

  1. Best Answer

    It's easier. Run the install for SQL Server 2008 on your server, choose the upgrade option. Done.

    Takes maybe 15 minutes, give or take. It really is remarkably easy.

    Edit: You still want to do a backup anyway right before upgrading, just in case. But more than likely you won't need it.

    • 14

  2. You'll also want to run the SQL Upgrade Advisor. This tool can help you determine if you're using any deprecated SQL features and will alert you to any gotcha's you might encounter.

    Once upgraded, to take advantage of many of SQL 2008's new features and enhancements, you'll want to change the compatibility level of your databases from 90 to 100.

    • 6

  3. You can do an in place upgrade, but you might want to take the oppertunity to do a clean build of the machine. In which case you can do a backup in 2005 and a restore in 2008.

    • 3

  4. I know this question was asked a while ago but I'm going to answer anyway.

    Call every vendor who's database you are running on your SQL 2005 server and confirm with them the software will run in 2008. As an example, BlackBerry server will not run in 2008 yet and you'll likely hose your server if you try it or even up with bad data.

    I called the vendors when I did this when moving from 2000 to 2005 and found one of our key piecies had issues. Quickly corrected by an sql script from the vender but I'm glad I knew about it before hand and what to expect.

    • 1

  5. Everyone has already answered how to upgrade, however be aware you can't downgrade if something goes wrong - So the advice to check compatibility with all vendor software you run is critical.

    Building a new machine is usually best, then backup and restore or attach/detach just like you were moving between SQL 2005 servers - Remember orphaned logins can be a problem if you are changing servers.

    • 1

  6. The intention is to migrate the databases (including reporting services) applications System Center Operations Manager and System Center Configuration Manager for SQL Server 2008 centralized. At a time when each of the servers there is a dedicated sql server 2005.

    I would appreciate your help (with links, pdf's, whatever).

    If they need any more information, just ask (although novice in this area).

    • 0