locked
How to move sql server 2000 database from one server box to server 2005 on another server box sql? RRS feed

  • Question

  • Hi,

     

    We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.

     

    When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?

     

    Thanks.

     

    Friday, August 10, 2007 8:11 PM

Answers

  • The 'best' way is to make a BACKUP of the SQL 2000 database, and RESTORE it on the SQL 2005 server.

     

    Alternatively, you can detach the database, copy the database file to the SQL 2005 server, and ATTACH the database to the SQL 2005 server.

    Friday, August 10, 2007 11:51 PM

All replies

  • The 'best' way is to make a BACKUP of the SQL 2000 database, and RESTORE it on the SQL 2005 server.

     

    Alternatively, you can detach the database, copy the database file to the SQL 2005 server, and ATTACH the database to the SQL 2005 server.

    Friday, August 10, 2007 11:51 PM
  • You need to perform as Arnie has rightly pointed out(backup/restore or detach and attach).

     

    refer this document, http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en 

     

    http://support.microsoft.com/kb/246133/ ---How to transfer logins b/w SQL 2k of sql2005

     

    if you want to practice this upgrade process make use of virtual labs refer, http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx

    you can also use copy database wizard
    Saturday, August 11, 2007 12:26 AM
  • Thanks a lot for your reply.

     

    I do not have the right to login the sql server1 to copy the detached .mdf file to sql server2 and then attach it. (sql server1 is the source server and sql server2 is the destination server.)  However, I can login in the sql server 2005 on the sql server2 using Windows authorization to use Task -> Copy database function. Unfornately, I get the following error. Any ideas about it? Moreover, where can I read event log in a sql server 2005? Thanks a lot again.

     

     

    Performing operation...

    - Add log for package (Success)

    - Add task for transferring database objects (Success)

    - Create package (Success)

    - Start SQL Server Agent Job (Success)

    - Execute SQL Server Agent Job (Error)

    Messages

    The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

     

    Monday, August 13, 2007 4:01 PM
  • To view the windows event viewer just right click my computer ---> click manage ---> under computer management beneath system tools you can see event viewer.

    Check the job history also as the job has been started but failed to complete......

    Tuesday, August 14, 2007 1:15 AM