locked
Migration from sql 2000 to sql 2012 RRS feed

  • Question

  • Hello Sir,

    I am going to upgrade from SQL 2000 to sql 2012.
    What are steps need to be taken care before starts.

    Is there any post upgradation issue have had experince?


    Cheer,

    • Edited by ESQL5 Thursday, June 11, 2015 12:32 PM
    Thursday, June 11, 2015 12:32 PM

Answers

  • Few important points:

    • Check what you are planning to upgrade, only databases or DTS packages, SSRS or cube also? It is quite important to understand that SSIS 2005 and later doesn't understand DTS very well and in that case you have to code them manually.
    • Check your target edition - From SQL Server 2000 to SQL Server 2008 R2 in this list. Unfortunately, SQL Server doesn't provide direct migration from 2000-2012.
    • Remember, SQL Server 64 bit will be installed only on 64 bit target system.
    • Run upgrade adviser - It will show you the issues you may encounter with possible workaround. 

    So your steps will be:

    • Take backup of database (.bak) of SQL Server 2000
    • Do in-place upgrade or if you are installing it on different server then skip this step
    • Restore .bak on SQL Server 2008 R2 edition
    • Change compatibility level of the databases to 100
    • Do in-place upgrade on SQL Server 2008R2 to SQL Server 2012
    • Change compatibility level of databases to 110
    • Update stats

    Note: You may need to reconfigure if you have separate installation

    • Database mails
    • Linked server

    Consider reading this whitepaper from Microsoft

    If you have SSRS, DTS and cubes installed on SQL Server 2000 then run upgrade adviser for them too.

    Hope this will help.


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Marked as answer by ESQL5 Thursday, June 11, 2015 2:11 PM
    Thursday, June 11, 2015 1:17 PM
  • Excellent Sir!
    • Marked as answer by ESQL5 Thursday, June 11, 2015 2:11 PM
    Thursday, June 11, 2015 2:11 PM

All replies

  • First of all , direct upgradation from sql 2000 to 2012 is not possible

    You need to do it in two steps first upgrade to SQL 2005/2008/ R2 and then do upgrade to 2012

    This might guide you in the process

    http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, June 11, 2015 12:39 PM
  • Hello,

    First, you can not upgrade directly from SQL Server 2000 to 2012, that's not supported, you have do an upgrade step between to an other SQL Server Version, e.g. first to SQL Server 2008; see Supported Version and Edition Upgrades

    Then you should run the upgrade advisors first to check your databases, see SQL Server 2014 Upgrade Advisor


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 11, 2015 12:40 PM
  • Thanks for reply!

    Any post up gradation issues?

    Cheers,

    Thursday, June 11, 2015 12:41 PM
  • Hello - There is NO direct upgrade path to upgrade SQL Server 2000 to SQL Server 2012. SQL Server 2012 supports upgrade from only: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1.

    Additionally, even if you try to restore backup taken on SQL Server 2000 over SQL Server 2012 Instance, you will end-up with error 3169 (Incompatible version)

    There will be an involvement of Intermediate step such that:

    • First you upgrade existing SQL Server 2000 SP4 instance to SQL Server 2008 and then
    • Upgrade SQL Server 2008 instance to SQL Server 2012

    More information:

    http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

    Hope this helps


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, June 11, 2015 12:44 PM
  • Few important points:

    • Check what you are planning to upgrade, only databases or DTS packages, SSRS or cube also? It is quite important to understand that SSIS 2005 and later doesn't understand DTS very well and in that case you have to code them manually.
    • Check your target edition - From SQL Server 2000 to SQL Server 2008 R2 in this list. Unfortunately, SQL Server doesn't provide direct migration from 2000-2012.
    • Remember, SQL Server 64 bit will be installed only on 64 bit target system.
    • Run upgrade adviser - It will show you the issues you may encounter with possible workaround. 

    So your steps will be:

    • Take backup of database (.bak) of SQL Server 2000
    • Do in-place upgrade or if you are installing it on different server then skip this step
    • Restore .bak on SQL Server 2008 R2 edition
    • Change compatibility level of the databases to 100
    • Do in-place upgrade on SQL Server 2008R2 to SQL Server 2012
    • Change compatibility level of databases to 110
    • Update stats

    Note: You may need to reconfigure if you have separate installation

    • Database mails
    • Linked server

    Consider reading this whitepaper from Microsoft

    If you have SSRS, DTS and cubes installed on SQL Server 2000 then run upgrade adviser for them too.

    Hope this will help.


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Marked as answer by ESQL5 Thursday, June 11, 2015 2:11 PM
    Thursday, June 11, 2015 1:17 PM
  • Hi,

    Check the migration heading on this page, under that you will find checklist that need to do for migration. There are videos those will talk about in-place and parallel migration. In your case you be doing Parallel as no direct upgrade available from SQL Server 2000 to SQL Server 2012.

    We have series of videos pretty much covers everything as Anuj mentioned in his reploy.

    Check out Migration Heading on the page

    Good luck!


    http://sqlage.blogspot.com/

    Thursday, June 11, 2015 1:23 PM
  • Excellent Sir!
    • Marked as answer by ESQL5 Thursday, June 11, 2015 2:11 PM
    Thursday, June 11, 2015 2:11 PM
  • I have come again in this post,upgrade advisor doesn't show any issues.

    Step 1:I have restored backup in sql server 2008 r2 from sql server 2000.

    Step 2: I have applied upgrade advisor 2012 on sql server 2008 r2 database.

    Step 3: It doesn't show any issues even there is code available in sql server 2000 database.

    Is there any step to find the incompatibilty issues by using advisor.

    Regards,

    Tuesday, June 23, 2015 6:45 PM