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