locked
Moving database from one sql 2000 to sql 2005 RRS feed

  • Question

  •  

    I have one data base which is around 600 GB. There are just 12 tables in this data base. This data base is in sql 2000. Some one alredy tried to move this data base from sql 2000 server to sql 2005 server. After moving data base, it was found that around 4 million records were missing in the sql 2005 in the data base. I am not sure how the data base was moved from one server to new server . I mean to say, I don't know if it was replicated or SSIS or Import/export. Now i was assigned to move this data base once again from sql 2000 to sql 2005. Any idea, what would be the best approach to move this data base? And guess, how 4 million records were missing after moving data base earlier?
    Friday, October 19, 2007 2:08 PM

Answers

  • Even if you use the Copy Database Wizard you can move the logins ,sp etc.......pls refer the below link with screenshots,

    http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1194281,00.html

    I prefer the Backup and restore and I feel it works well, I have restored such huge dbs without losing any objects ! It should work well. The copy database wizard transfers the entire ldf and mdf and attaches them.

     

    Regards

    Deepak

    Friday, October 19, 2007 9:04 PM
  • Yes you are right you cannot perform log shipping but you have the option of using transactional replication also ! where you can chose only those objects you wish to replicate ! but frequent DML operations might become an overhead so its advisable that you move to sql 2005 and you can implement database mirroing feature Smile which replicates the data in quick time and also automatic failover is also available !

    Regards
    Deepak
    Tuesday, October 23, 2007 11:52 AM

All replies

  • There are 3 options for you,
    1. Backup and Restore database
    2. Detach and Attach
    database
    3. Copy database wizard (requires the sql 2005 which is the destination to have SP2)

    If you can afford downtime you can chose the 2nd option else you are left with 1 or 3.

    Regards
    Deepak

    Friday, October 19, 2007 3:00 PM
  •  

    If I use no 3 options, i can't move data base objects such as jobs, store procedure, log in id etc. I think i can move all these if i follow no.1 option. what about no 3 option? Actually, as the size of the data base is too big, i am thinking to move single table at a time. in that case, i believe I can follow 3rd option only. isn't it? is there any better solution?
    Friday, October 19, 2007 3:26 PM
  • Even if you use the Copy Database Wizard you can move the logins ,sp etc.......pls refer the below link with screenshots,

    http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1194281,00.html

    I prefer the Backup and restore and I feel it works well, I have restored such huge dbs without losing any objects ! It should work well. The copy database wizard transfers the entire ldf and mdf and attaches them.

     

    Regards

    Deepak

    Friday, October 19, 2007 9:04 PM
  • Thanks for your suggeston and I will also do 1st Back up (Full +Diff +Log) and then restore it in different server. But still I have one doubt. As I mentioned earlier, it is very big data base(around 600GB). So that in this case, it will take too much time for doing back up and restore. But every day, thousands of data are added in the original data base. Even if I do log shipping back up, I think still some data will be missing in the destination server.In this case, can you suggest me, what would be the best approach so that data can match with both destination and source server? I can't do log shipping as destination(sql 2000) and source server (sql 2005) are in two different platform.
    Saturday, October 20, 2007 4:11 PM
  • Yes you are right you cannot perform log shipping but you have the option of using transactional replication also ! where you can chose only those objects you wish to replicate ! but frequent DML operations might become an overhead so its advisable that you move to sql 2005 and you can implement database mirroing feature Smile which replicates the data in quick time and also automatic failover is also available !

    Regards
    Deepak
    Tuesday, October 23, 2007 11:52 AM