locked
Export SQL 2005 to 2008 R2 on new server RRS feed

  • Question

  • We're in the process of moving all data from a Win2003 R2 server to Win2008 R2 (both virtualized on Hyper-V). In the process we need to move the databases, reports, jobs, etc... from SQL 2005 to SQL 2008 R2. What's the best process for doing this?

     
    Tuesday, May 15, 2012 7:40 PM

Answers

  • Hello,

    Use the following article to transfer logins:

    http://support.microsoft.com/kb/918992

    To move reports from one server to another, read the following thread:

    http://social.msdn.microsoft.com/Forums/en-CA/sqlreportingservices/thread/1765c83c-c4df-460c-a6fa-c1db4eb41fe1

    About the databases, read the following:

    http://msdn.microsoft.com/en-us/library/ms187858(v=sql.105).aspx

    To transfer jobs, try to script them or use the Transfer Jobs Task in SSIS:

    http://msdn.microsoft.com/en-us/library/ms137568(v=sql.105).aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Carltonw1 Wednesday, May 16, 2012 7:29 PM
    Wednesday, May 16, 2012 4:33 AM
  • Alberto,

    Thanks for the links. The link you sent about moving the databases using detach, but since this is a live system I'd like to use a different method. I tried using Import from the new server, but the default table sizes caused errors. Is there another way to copy the databases/tables/data over to the new system w/o having to take take down the live site?

    you could use backup & restore and setup all required logins, jobs, reports, etc.

    As soon you've tested the migrated objects you can take another full backup of the database(s), restore them on the new server with standby. At the time you want to switch, take another transaction log backup, take the old server offline and restore it on the new server and take the database(s) online.

    Alternatively you can use mirroring from SQL 2005 to SQL 2008R2 and do a manual fail over to the new server. Mirroring to higher version is supported for upgrading server and therefore it is supported. once failed over to higher version of SQL Server you can fail over to the old server but that is not what you want/need.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Carltonw1 Wednesday, May 16, 2012 7:29 PM
    Wednesday, May 16, 2012 3:20 PM

All replies

  • Hello,

    Use the following article to transfer logins:

    http://support.microsoft.com/kb/918992

    To move reports from one server to another, read the following thread:

    http://social.msdn.microsoft.com/Forums/en-CA/sqlreportingservices/thread/1765c83c-c4df-460c-a6fa-c1db4eb41fe1

    About the databases, read the following:

    http://msdn.microsoft.com/en-us/library/ms187858(v=sql.105).aspx

    To transfer jobs, try to script them or use the Transfer Jobs Task in SSIS:

    http://msdn.microsoft.com/en-us/library/ms137568(v=sql.105).aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Carltonw1 Wednesday, May 16, 2012 7:29 PM
    Wednesday, May 16, 2012 4:33 AM
  • Alberto,

    Thanks for the links. The link you sent about moving the databases using detach, but since this is a live system I'd like to use a different method. I tried using Import from the new server, but the default table sizes caused errors. Is there another way to copy the databases/tables/data over to the new system w/o having to take take down the live site?

    Wednesday, May 16, 2012 2:31 PM
  • Hello,

    Try the following tool with no downtime:

    http://msdn.microsoft.com/en-us/library/ms188664.aspx

    Read the limitations first:

    http://msdn.microsoft.com/en-us/library/ms188664.aspx#Restrictions   

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, May 16, 2012 2:59 PM
  • Alberto,

    Thanks for the links. The link you sent about moving the databases using detach, but since this is a live system I'd like to use a different method. I tried using Import from the new server, but the default table sizes caused errors. Is there another way to copy the databases/tables/data over to the new system w/o having to take take down the live site?

    you could use backup & restore and setup all required logins, jobs, reports, etc.

    As soon you've tested the migrated objects you can take another full backup of the database(s), restore them on the new server with standby. At the time you want to switch, take another transaction log backup, take the old server offline and restore it on the new server and take the database(s) online.

    Alternatively you can use mirroring from SQL 2005 to SQL 2008R2 and do a manual fail over to the new server. Mirroring to higher version is supported for upgrading server and therefore it is supported. once failed over to higher version of SQL Server you can fail over to the old server but that is not what you want/need.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Carltonw1 Wednesday, May 16, 2012 7:29 PM
    Wednesday, May 16, 2012 3:20 PM
  • Your best bet would be to take a Backup and Restore it in the New Server. Import Export is usually preferred if there are few objects to be moved between the servers. Import Export Objects are not usually safe and does not guarantee data consistency. Plus you may run into foreign key constraints errors while moving objects. 

    Use backup and restore to be in the safe side. This will also leave the source server database in ONLINE state while you take the backup.
    Use tools like Redgate Backup pro if you want to move Huge database in case the SQL 2005 does not have any database compression . But SQL 2008 has the native backup compression built in .

    For moving jobs you have to migrate the MSDB system database. Just Backup and restore the MSDB database.
    Moving jobs from SQL 2005 to SQL 2008R2 you will run into version issues which can be resolved by scripting out the jobs from SQL 2005

    Hope this will help....

     
    Thursday, October 25, 2012 3:57 PM