Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

up vote 14 down vote accepted

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.

share|improve this answer
1  
just to be safe, backup up your existing databases (which u should be doing 'regularly' anyways.. right?!!!) –  Pure.Krome May 1 '09 at 0: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. –  Sung May 1 '09 at 0:01
    
@Pure: Of course. Full backups 2x a week and 5 diff. backups like 6x a day. –  Sung May 1 '09 at 0: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 8 '11 at 21:00
add comment

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.

share|improve this answer
    
I remember seeing SQL Server Best Practices for 2005. It looks like SQL Upgrade Advisor seems to have a similar functionality. Thank you Elias –  Sung May 6 '09 at 17:28
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.