Results 1 to 11 of 11

Thread: 6.5 Restore

  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unhappy Unanswered: 6.5 Restore

    Hi all,

    I'm trying to move a SQL Server 6.5 database from one almost extinct NT4 box, to a whizz-bang only-almost-dead NT4 box. These boxes live in different domains, and CANNOT be networked together.

    The original box has a daily backup routine that runs daily, with no errors. A colleague copied the data, log files and a single backup file from the overnight job from the original box, to the "new" box over the weekend, but when I go to restore the database from the backup file, the new installation doesn't seem to recognise a backup within this file. We have a backup file from that database that is about 2 years old, which restores perfectly well, but obviously, doesn't quite meet our needs data wise.

    Is there an sp_attachdb (or equivalent) in SQL Server 6.5 that I can use to create a new database with the orignal data and log file??

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can do something like sp_attach_db, but in SQL 6.5 it is pure smoke-and-mirrors, and almost completely unsupported.

    Before we even head down that path, the problem you describe was common when trying to install a backup made with a later service pack than the machine trying to do the restore. As an example, if you made a backup with sp6 then tried to restore it using RTM (or even sp4) the server wouldn't even recognize it as a backup file, much less actually restore it!

    My advice is to find out what service pack was applied to the machine making the backup, then bring the machine doing the restore to the same (or later) service pack.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    Thanks Pat.

    I've just checked and both machines are showing...

    Microsoft SQL Server 6.50 - 6.50.416 (Intel X86)
    Jan 23 1999 14:10:24

    Also, the backup I CAN restore to the "new" box comes from the same original box as the backup that is unrecognised (admittedly some time ago, and therefore possibly some SP's ago)

    The backup I can't restore on the new box, doesn't show any Backup Information in the "Add file" window of the restore options. On the original server however, the Backup Information shows just fine.

    Is the create using original data and log files option really not advisable??

    Thanks in advance

    zinedunc

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by zinedunc
    Is the create using original data and log files option really not advisable??
    I would only try to attach a 6.5 database on a server that was 100% expendable, one that I didn't care if I had to reinstall both NT and SQL if necessary.

    Have you tried using the LOAD DATABASE command from ISQL/W to see what results it returns?

    -PatP

  5. #5
    Join Date
    Jan 2004
    Posts
    32
    I haven't tried that yet Pat - I've had very little experience on 6.5, and any 6.5 restores I've done in the past have been via the standard restore from backup files, without any issues.

    I only have a backup file (created by a scheduled Backup Device, which the "new" server doesn't seem to like) and the original data and log files (copied from the original server when SQL Server service was stopped) - which of these, if any, can be used in the LOAD DATABASE statement please??

  6. #6
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    i don't know if this is possible, but could you bcp out all the tables and data individually and then bcp them into a fresh datbase on the "new" box? it would take an age, but i think it could be done.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is this a problem of the database will not restore, or a problem of SQL Server can not "read" the backup file? Try running this:
    Code:
    restore headeronly from disk = 'your backup file'
    Been a while since I used 6.5, so it may be "load headeronly" as opposed to "restore headeronly".

    This command will only read the header information from the backup file, so you can see what is in it.

  8. #8
    Join Date
    Jan 2004
    Posts
    32
    Thanks to Pat, cruickshanks and MCrowley for your suggestions.

    "load headeronly" using the backup file proved that SQL Server could read the file, and I currently have a "load database" command running against that file, that after an hour or so (thankfully) has not returned any errors.

    Here's hoping all is well in the morning.

    Thanks again.

  9. #9
    Join Date
    Jan 2004
    Posts
    32
    Ah, the "load database" I left running overnight didn't work.

    I didn't receive an error as such, but got the following message returned.....

    "This command did not return any data, and it did not return any rows"

    The original box here is not on site, and cannot be accessed from my network, so it's not as simple as taking another backup and copying it across.

    Any ideas what else I can try with this backup file, or the original data and log files to save me a 200 mile drive today??

    Thanks in advance

  10. #10
    Join Date
    Jan 2004
    Posts
    32
    Ah, OK, panic over.....

    The data appears to be in place, am just waiting for confirmation following testing through the application.

    Thanks for all your help

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, we should have warned you that "no rows returned" was the expected response. It's been a while since I've used 6.5 on any regular basis, so I don't always think to forewarn people about its quirks.

    -PatP

Posting Permissions

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