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??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|||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|||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|||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??|||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.|||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:

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.|||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.|||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|||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|||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