Question:
How do I take a SQL 6.5 database that is residing on one machine and move the entire database to another machine?
Answer:
I have three methods for you to investigate: one using Transfer Manager, one using Dump and Load, and one using scripts and bcp. Which one is right for you depends on the database size and the time in which you have to do it.
If the database is not large and both servers are on the same network, you could move the structure and data using Transfer Manager. This is painless and brainless. Just be sure you have checked “dependent objects” to move your integrity constraints and you should be fine.
If the database is large, however, you’ll need a different approach. The easiest and quickest thing to do would be to run sp_revdatabase and sp_helpdevice to document the state of the source machine’s copy of the database. Use the output from the sps to recreate the devices exactly as they are on the source machine. Dump the database (to disk if you can) and then load that dump onto the target machine. You will get an error on the target machine to the effect that msdb is missing sysbackuphistory, which is to be expected because there never was a backup made on the target machine. Ignore the message and continue loading the source machine’s dump to the new target.
The hardest way to copy your database (but the method with the most control over the process) is to script the source database out and rebuild it on the target, then bcp the data out of the source and into the target.
