Upgrade from SQL Server 2005 to SQL Server 2008 R2

Posted on 2011-10-25
Last Modified: 2012-05-12
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?
Question by:luyan
    LVL 27

    Accepted Solution

    For your 2005 reports all you have to do is re-deploy them from 2008
    LVL 39

    Assisted Solution

    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.
    LVL 1

    Expert Comment

    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:

    In order to migrate Reporting Services, this is the step-by-step process for success migration:
    And for DTS:

    Good luck

    Author Closing Comment


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now