locked
the faster way to move databases to SQL Server 2008 RRS feed

  • Question

  • Hello all,

    I am going to upgrade my production SQL 2000 server to SQL Server 2008 x64 FOC on windows server 2008 IA and I have very diffucult situation which is I can't downtime my current production server more than 3 hours to move latest production databases copy to the new destination (SQL 2008) I have on the Current production (SQL 2000)

    • 120 databases
    • Size 300 GB

    I tried to think ofmany ways to achive this goal but without result .

    please I need a hand here . any idea\clue is welcome

    thanks in advance.

     


    Volcano "DATA is the Focus, Business is the Target”"
    Sunday, May 9, 2010 11:58 AM

Answers

  • Hi,
    In your case, the quickest method is to add a hard disk to your server in SQL 2000. Make a local copy after stop SQL Service. Then move this disk to the second server, and run a script to attach you 120 databases.


    Another method can be performing, but a little bit long. Put all your databases in recovery model in FULL. Then, perform a backup full on all your databases and restore it on the new server. And the day of the migration, perform a backup log on all databases and restore the backup log on the new server. This method will minimize the downtime on your production.

    Have a good day,
     


    Michel DEGREMONT , my blog SQL Server
    • Marked as answer by Tom Li - MSFT Thursday, May 20, 2010 5:02 AM
    Sunday, May 9, 2010 7:13 PM
  • I propose the latter method described by Michel above. You could look a detailed article by Jonathan Kehayias on this topic and it’s a very good read and might help you in the right direction.

    http://www.sqlservercentral.com/articles/Backup+/+Restore/66962/


    Sankar Reddy http://sankarreddy.com/
    • Marked as answer by Tom Li - MSFT Thursday, May 20, 2010 5:02 AM
    Sunday, May 9, 2010 10:44 PM

All replies

  • Hi,
    In your case, the quickest method is to add a hard disk to your server in SQL 2000. Make a local copy after stop SQL Service. Then move this disk to the second server, and run a script to attach you 120 databases.


    Another method can be performing, but a little bit long. Put all your databases in recovery model in FULL. Then, perform a backup full on all your databases and restore it on the new server. And the day of the migration, perform a backup log on all databases and restore the backup log on the new server. This method will minimize the downtime on your production.

    Have a good day,
     


    Michel DEGREMONT , my blog SQL Server
    • Marked as answer by Tom Li - MSFT Thursday, May 20, 2010 5:02 AM
    Sunday, May 9, 2010 7:13 PM
  • I propose the latter method described by Michel above. You could look a detailed article by Jonathan Kehayias on this topic and it’s a very good read and might help you in the right direction.

    http://www.sqlservercentral.com/articles/Backup+/+Restore/66962/


    Sankar Reddy http://sankarreddy.com/
    • Marked as answer by Tom Li - MSFT Thursday, May 20, 2010 5:02 AM
    Sunday, May 9, 2010 10:44 PM