10

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?

dance2die
  • 1,961
  • 7
  • 31
  • 40

6 Answers6

14

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.

Wedge
  • 1,597
  • 11
  • 16
  • 1
    just to be safe, backup up your existing databases (which u should be doing 'regularly' anyways.. right?!!!) – Pure.Krome May 01 '09 at 00:00
  • Marked as answer: I had no idea that "SQL Installation Center" had "Upgrade from SQL Server 2000 or SQL Server 2005"... And I have also found links MSDN upgrade documentation. thank you for pointing me to a right direction. – dance2die May 01 '09 at 00:01
  • @Pure: Of course. Full backups 2x a week and 5 diff. backups like 6x a day. – dance2die May 01 '09 at 00:02
  • Do a file backup on your master, tempdb, and msdb databases. That means taking your server offline. If you need to roll back you can't roll back if your system databases are upgraded to a later version. – Shannon Lowder Apr 08 '11 at 21:00
6

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.

Elias
  • 101
  • 3
  • I remember seeing SQL Server Best Practices for 2005. It looks like SQL Upgrade Advisor seems to have a similar functionality. Thank you Elias – dance2die May 06 '09 at 17:28
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.

JPrescottSanders
  • 519
  • 2
  • 5
  • 12
1

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.

SpaceManSpiff
  • 2,547
  • 18
  • 19
1

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.

sysadmin1138
  • 131,083
  • 18
  • 173
  • 296
Steve Radich-BitShop.com
  • 573
  • 3
  • 7
0

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).

Starfish
  • 2,716
  • 24
  • 28