• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Upgrade from SQL Server 2005 to SQL Server 2008 R2

Our current SQL Server is SQL 2005 Enterprise Edition, we will move to SQL Server 2008 R2 Enterprise Edition. We have several hundred databases need to upgrade to SQL 2008 R2.

We could detach databases from SQL 2005 then attach them to SQL 2008 R2. Or backup databases from SQL 2005 and then restore on SQL 2008 R2. Is it in the right way? Do you have any good suggestions about this upgrade? How about reporting services upgrade and data transformation service packages upgrade?
2 Solutions
For your 2005 reports all you have to do is re-deploy them from 2008
lcohanDatabase AnalystCommented:
I think the upgrade depends on your business - can you afford the downtime?
If yes, then any of the two - detach(copy)attach or backup/restore would do the upgrade from 2005 to 2008 for you - choose whatever is easier for you to do on the 200+ db's.

For the SSRS reports unfortunatley there is no magic 100% working solution even though there are many on the internet. I tried them all for my few hundred reports and the only safe one (unfortunatly) was to open each report in BI and you will get notification at the time that they get upgraded to 2008 (so better keep a copy of 2005 safe because there's no roll back!! from 2008) then I had to build/deploy to the new 2008 SSRS. Good luck.
I recommend the detach and attach proccess for databases migration. After the migration, databases remains compatibility level version and collation.
You should use this procedure: http://msdn.microsoft.com/en-us/library/ms189625.aspx

In order to migrate Reporting Services, this is the step-by-step process for success migration: http://technet.microsoft.com/en-us/library/ms143724.aspx
And for DTS: http://technet.microsoft.com/en-us/library/ms143501.aspx

Good luck
luyanAuthor Commented:

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now