Copying a Database from One Machine to Another

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job