Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Unanswered: More 6.5 upgrading to 7 help

    Thanks for the help so far,
    I have seven, old 6.5 databases to bring into a fresh 6.5 install, they all have the same tables, veiws and sproc's. Should I create a device for each database or can I/should I use 7 devices? I want to end up with 7 mdf files in the end.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If I were doing the recovery, I would:
    1. Set up the fresh SQL 6.5 installation
    2. Load the devices (in your case the DAT file)
    3. Recover the databases from the devices on the SQL 6.5 instance
    4. Backup the databases from the 6.5 instance.
    5. Restore the SQL 6.5 backups onto a SQL 2000 instance (which should set the compatibility level)
    6. Backup the databases from the SQL 2000 instance
    7. Restore the SQL 2000 backups onto SQL 2008
    8. Backup the databases from the SQL 2008 instance
    9. Restore the SQL 2008 backups onto SQL 2012
    10. Run DBCC CHECKDB against every database
    The stopover at SQL 2008 may not be necessary, but I'd do it to ensure that I followed the supportability chain to the letter in case I needed support in the future. I've seen cases where data appeared good until either quarter end or year end processing turned up a problem and if you know that you followed the supportability chain you can often get significantly better support from MS-PSS if you need it.

    At this point, I'd be comfortable that the data is complete and intact on SQL 2012.

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

  3. #3
    Join Date
    Sep 2010
    Posts
    22
    Pat,
    step 5 where I restore a 6.5 backup (.dat file?) to 2000 is where my eyes glaze over but I'll give it a try thursday. thanks for the reminder to run the DBCC command and the details about suportability, awesome, thanks! I'll let you know how it goes.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, the DUMP DATABASE command produces DMP files by default, but you can name the backups whatever suits you... I once had a client name their backups WK1 which was hysterical to me because that was the extension used by an old spreadsheet (like Excel).

    You restore those backups to SQL 2000 with the RESTORE DATABASE and go on your merry way.

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

  5. #5
    Join Date
    Sep 2010
    Posts
    22
    so, Dump Database mydatabase, I'll look it up, I've never used that, so far I've just done gui backupss.. thanks a bunch, I appreciate it..

  6. #6
    Join Date
    Feb 2014
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    [*]Backup the databases from the 6.5 instance.[*]Restore the SQL 6.5 backups onto a SQL 2000 instance (which should set the compatibility level)
    Pat,
    You're the only person I've seen that says you can restore a 6.5 backup in 2000. Did MS change something pretty late in the game because all my other research says you must use the Upgrade tools (though those posts/articles are all from early to mid 2000s)???

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In your case (doing work for a client), you may wish to follow the Microsoft recommended SQL 6.5 to SQL 2000 Upgrade using the two computer method. This is a bit more cumbersome than a simple backup/restore but it is also fully supported... There are some edge cases where even when my method works, it may not be fully supported by Microsoft.

    This is ancient old now, so I'm foggy on the details of which SQL 2000 Service Pack changed this behavior. I believe that it was sp5, but that was also more than a decade ago!

    Please use the provided upgrade from Microsoft. They can give much better support than I can if you run into problems!

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

  8. #8
    Join Date
    Feb 2014
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    In your case (doing work for a client), you may wish to follow the Microsoft recommended SQL 6.5 to SQL 2000 Upgrade using the two computer method. This is a bit more cumbersome than a simple backup/restore but it is also fully supported... There are some edge cases where even when my method works, it may not be fully supported by Microsoft.

    This is ancient old now, so I'm foggy on the details of which SQL 2000 Service Pack changed this behavior. I believe that it was sp5, but that was also more than a decade ago!

    Please use the provided upgrade from Microsoft. They can give much better support than I can if you run into problems!
    Thanks for the reply. For testing I'm not worried about support so I'll go ahead and see if your method works with the MSDE download.

    I had looked at that doc quite a bit, of course, but not being a SQL Server DBA (I mostly use Oracle), I'm not familiar with a few things they mentioned though I'm sure I could look it up. What bothered me was they said, "Marks server and databases as moved". What does that mean in 6.5 speak? I don't want to alter the current instance at all since they still need it until I can upgrade the database and modify the VB program.

  9. #9
    Join Date
    Feb 2014
    Posts
    4
    There was no SP5 that I could find and SP4 doesn't do the trick either. I get message 3242, which seems to indicate the backup set wasn't generated from version 7 or later.

  10. #10
    Join Date
    Feb 2014
    Posts
    5
    What about SQL 2014 ?

Posting Permissions

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