Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    24

    Unanswered: Migrating form MS SQL2000 to MS SQL2008

    i've got an outdated but heavily used SQL-Server 2000. In fact it is the only component of an old SBS2000 that is still in Use. Not only the Software ist outdated, the Hardware is old too.

    So i got to migrate. I downloaded an SQL 2008 R2 Express-Edition as a playground and startet testing before i gonna buy a Standard-Editon Server.

    Doing some imports, several questions came up that i couldn't solve myself. Hence a short answer ( may be just a link to an good article on that topics ) is apreciated very much. Here is my list of questions.


    1. my SQL2000 Database is 32Bit, i read that it would be better to migrate to a 32Bit Edition then. Are there disadvantages when migrating to 64Bit? Any problems to cope with?

    2. I was unable to just load a 2000 .bak backup into the SQL2008-Database. Is there no way to do so or did i iss anything?

    3. I used the import via DTS then. This way leads to loss of Indixes and Identity-Columns. I assume that i have to build SQL-Scripts for creating empty tables containing identity-Columns and triggers and then use DTS-Importing on them (IDENTIY_COPY) !? Is this way of building a Skeleton first and then importing Data the best way?

    4. Is there no comfortable way to get a database with all its tables and there corresponding identityIDs, Indexes,Triggers and Stored-Procedures transfered? Or have i just missed something in transferring databases.

    5. DTS-Importing always turns Views to complete tables. I mean it's easy to recreate the views by a script, but is there really no way in DTS to import Views ans Views???

    The last time i did such an migration it has been the Migration from MS SQL 6.0 (SBS 4.5) to MS SQL 2000 ( SBS 2000) and this is by now almost a decade away . So i remember we did it this way, but ist there 10 years later, no better way?


    Any hints,words, tipps are apreciated as it is better to learn before i do a real migration and i perhaps baught the wrong version ( 32bit vs. 64bit).


    Greetings and Thanx in advance


    wegus
    Last edited by wegus; 07-04-11 at 03:48.
    an Apple a day keeps Dr. Watson away !

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only real disadvantages to converting from 32 bit to 64 bit is that you lose the opportunity to spend hours trying to deal with memory problems. Without this, yoiu actually work instead of spending hours trying to figure out work arounds for problems that don't exist in the 64 bit platform... In case you're still wondering, go 64 bit if you have the opportunity!

    I'm nearly certain that you can restore a SQL 2000 onto a SQL 2008 R2 server, but I don't have either one handy to confirm that thought... I'm not sure what is causing you grief, so please post whatever diagnostic messages you get when you do the restore.

    The one thing I can guarantee that you can NOT restore is the system databases, especially master. The only way to take them from SQL 2000 to SQL 2008 R2 would be to do an "in place upgrade" of your server. This is probably beyond your current skill set, but this would be the only way to preserve the logins and other system settings.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2006
    Posts
    24
    Thank you for your 64bit advice. A very good point!

    Well system/master don't bother me much. Things in there will be replaced/rebuild quite quick in our case.

    What is anoying is the trouble you run in when using DTS. I made a Skeleton rebuild by generating empty tables and triggers, indexes and views first and then importing the content via DTS that just works fine.

    i only had to disable all timestamp-columns as they can not be inserted and i do have to activate IDENTITY_INSERT on most tables.

    Well as i read, i could create and save the DTS Script when using a real Server ( i am just trying with the Express Edition by now). The Express Edition obviously can't start the SQL Agent so i can't test our cron-jobs and Backup. But those things surely work in the commercial version.

    Quote Originally Posted by Pat Phelan
    I'm nearly certain that you can restore a SQL 2000 onto a SQL 2008 R2 server, but I don't have either one handy to confirm that thought... I'm not sure what is causing you grief, so please post whatever diagnostic messages you get when you do the restore.
    Thank you, next time i bust my Testdatabase i'll retry an post results here !
    an Apple a day keeps Dr. Watson away !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •