locked
Data Migration SQL Server 2000 to 2008R2 RRS feed

  • Question

  • Hi,

    I need to migrate a 3TB SQL Server 2000 database into SQL Server 2008R2 with minimum downtime as possible.  Having never attempted anything like this before I would be glad of any tips and advice anyone is able to provide.

    Thanks

    Oliver

      

    Friday, April 5, 2013 3:52 PM

Answers

  • The Best  way to do it is take Full backup on sql 2000(or use the latest one, if you have) and restore on to sql2008R2 instance in No recovery mode)

    If your sql2000 instance is in full recovery mode, then apply the latest differential backup + all the log backups taken after the differential backups. all the backups upto this point should be applied in no recovery mode. Now, disconnect all the applications accessing the db and Take one last log backup on the sql2000 and set this sql2000 database to offline mode. Apply this log backup on the sql2008R2, with Recovery mode.
    --OR--
    If your sql2000 is in simple recovery mode, then disconnect all the applications accessing the db and Take differentiallog backup on the sql2000 and set this sql2000 database to offline mode. Apply this differntial backup on the sql2008R2, with Recovery mode.

    make sure you all the database users have valid logins mapped with appropiate permissions on the sql2008R2 instance.
    So, It all depends on how your current backups are scheduled on your sql2000 database..

    Please refer this for some understanding before you start doing this : http://www.brentozar.com/archive/2012/08/backup-basics-free-video/


    Hope it Helps!!


    • Edited by Stan210 Friday, April 5, 2013 4:38 PM
    • Marked as answer by Allen Li - MSFT Monday, April 15, 2013 6:28 AM
    Friday, April 5, 2013 4:35 PM

All replies

  • The Best  way to do it is take Full backup on sql 2000(or use the latest one, if you have) and restore on to sql2008R2 instance in No recovery mode)

    If your sql2000 instance is in full recovery mode, then apply the latest differential backup + all the log backups taken after the differential backups. all the backups upto this point should be applied in no recovery mode. Now, disconnect all the applications accessing the db and Take one last log backup on the sql2000 and set this sql2000 database to offline mode. Apply this log backup on the sql2008R2, with Recovery mode.
    --OR--
    If your sql2000 is in simple recovery mode, then disconnect all the applications accessing the db and Take differentiallog backup on the sql2000 and set this sql2000 database to offline mode. Apply this differntial backup on the sql2008R2, with Recovery mode.

    make sure you all the database users have valid logins mapped with appropiate permissions on the sql2008R2 instance.
    So, It all depends on how your current backups are scheduled on your sql2000 database..

    Please refer this for some understanding before you start doing this : http://www.brentozar.com/archive/2012/08/backup-basics-free-video/


    Hope it Helps!!


    • Edited by Stan210 Friday, April 5, 2013 4:38 PM
    • Marked as answer by Allen Li - MSFT Monday, April 15, 2013 6:28 AM
    Friday, April 5, 2013 4:35 PM
  • Hi Oliver,

    Please see the following MSDN article which will tell you what service pack on 2000 and editions are supported. 

    MSDN: SQL Version and Edition Upgrades

    If you had access to the PASS 2012 DVD set, you could watch my session on upgrade :) but failing that, please make sure you fully investigate (and trial) before actually performing it. Use tools such as the MAP Toolkit and Upgrade Advisor -as well as a whole batch of other upgrade tools. Don't forget to set compatibility level to SQL 2008R2 once your upgrade finally happens.

    You should also look at configuring upstream log shipping in this scenario to automate as much as possible and reduce the downtime/ effort prior to upgrade.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog|SQLCloud)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Watch my sessions at the PASS Summit 2012 and SQLBits

    Monday, April 8, 2013 9:14 PM