locked
Steps to migration SQL Server 2000 SP4(32bit ) to SQL Server 2008 R2(64bit) RRS feed

  • Question

  • My issue is what are there steps to migration SQL Server 2000 SP4(32bit ) to SQL Server 2008 R2(64bit)? In addition to that how to move DTS to new server(SQL 2008) & how to resolve T-SQL code change with in the version as an example in SQL 2000 xp_sendmail() its change in SQL 2008 sp_db_sendmail()... look forward to have a solid response.
    Wednesday, November 13, 2013 7:25 PM

Answers

  • Hi SQL_find,

    SQL Server 2000 was out of support since April,2013. You can try to install SQL Server 2005 or later version. According to your description, usually, the operating system do match SQL Server platform. For example, you install a 32bit SQL Server on 32bit Windows, and if you have the x64 platform of Windows, you usually install the x64 SQL Server. When you migrate SQL Server 2000 (32 bit) to SQL Server 2008 R2 (64bit), you cannot restore system databases (master, model, tempdb, msdb) to a different version, not even a different service pack or hotfix level.

    For more information, you can review the following blog.
    http://blogs.msdn.com/b/cindygross/archive/2009/07/07/x64-windows-upgrade-from-32bit-sql-server-to-64bit-sql-server.aspx

    In addition, if you want to migrate SQL Server to other with the same bit. You  can review the following similar article about migration steps.
    http://blogs.technet.com/b/mdegre/archive/2009/07/21/migration-sql-server-2000-to-sql-server-2008.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 14, 2013 9:50 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:40 AM
    Thursday, November 14, 2013 9:44 AM
  • Migration Environment:

    Db Server:MSSQL Server 2000(32bit) OS:Win2003  ->>>>DB SERVER: MSSQL Server 2008 R2 OS: Windows 2008 Data-center Edition

    Hi SQL_find,

    We need to verify which version of SQL Server you have installed on the platform of Windows 2008 Data-center Edition, 32 bit or 64 bit? If it is 64bit. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. To run packages in 32-bit mode on a 64-bit computer, usually, you have to install the following components:

    •The 32-bit DTS runtime.

    •The 32-bit version of the Integration Services runtime and command prompt utilities.

    For more information about DTS, you can review the following article.
    Support for SQL Server 2000 DTS in SQL Server 2008 R2 : http://msdn.microsoft.com/en-us/library/bb500440.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:40 AM
    Monday, November 25, 2013 3:22 AM

All replies

  • Hi SQL_find,

    SQL Server 2000 was out of support since April,2013. You can try to install SQL Server 2005 or later version. According to your description, usually, the operating system do match SQL Server platform. For example, you install a 32bit SQL Server on 32bit Windows, and if you have the x64 platform of Windows, you usually install the x64 SQL Server. When you migrate SQL Server 2000 (32 bit) to SQL Server 2008 R2 (64bit), you cannot restore system databases (master, model, tempdb, msdb) to a different version, not even a different service pack or hotfix level.

    For more information, you can review the following blog.
    http://blogs.msdn.com/b/cindygross/archive/2009/07/07/x64-windows-upgrade-from-32bit-sql-server-to-64bit-sql-server.aspx

    In addition, if you want to migrate SQL Server to other with the same bit. You  can review the following similar article about migration steps.
    http://blogs.technet.com/b/mdegre/archive/2009/07/21/migration-sql-server-2000-to-sql-server-2008.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 14, 2013 9:50 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:40 AM
    Thursday, November 14, 2013 9:44 AM
  • Thanks Sofiya Li for your info. I am not taking the system database to new instance.My steps of migration are as bellow:

    Migration Environment:

    Db Server:MSSQL Server 2000(32bit) OS:Win2003  ->>>>DB SERVER: MSSQL Server 2008 R2 OS: Windows 2008 Data-center Edition

    1. Detach & attaching the .mdf &.ldf files.  between the DB servers.
    2. Scripts all jobs and run it to new server.
    3. Script all user,roles and run it to new server.
    4. Reestablish replications & link server.

    But what I will do with DTS and let me know any else I need to do more?

    Thanks,

    SQL Programmer

    Thursday, November 14, 2013 5:33 PM
  • Migration Environment:

    Db Server:MSSQL Server 2000(32bit) OS:Win2003  ->>>>DB SERVER: MSSQL Server 2008 R2 OS: Windows 2008 Data-center Edition

    Hi SQL_find,

    We need to verify which version of SQL Server you have installed on the platform of Windows 2008 Data-center Edition, 32 bit or 64 bit? If it is 64bit. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. To run packages in 32-bit mode on a 64-bit computer, usually, you have to install the following components:

    •The 32-bit DTS runtime.

    •The 32-bit version of the Integration Services runtime and command prompt utilities.

    For more information about DTS, you can review the following article.
    Support for SQL Server 2000 DTS in SQL Server 2008 R2 : http://msdn.microsoft.com/en-us/library/bb500440.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:40 AM
    Monday, November 25, 2013 3:22 AM