Copy SQL DBs from one machine to another

I have a replacement for an existing SQL Server machine.  Both have SQL 2000.  Old one has around 30 databases that I need to move/copy to the new machine.  What's the best way to do this?  Is there a quick way?  Both machines are on the same network domain using Windows Authentication and are running Windows 2000.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Open Enterprise Manager.
Select Source SQL Server
Select Databases
Right Click on a DB
Select All Tasks
Select Export Data

DTS Wizard:
1) DTS Source (already filled in)
2) DTS Destination (fill in fields)
* Create NEW DB from drop-down
3) Select Copy Database Objects

Note: SQL Logins will need to be created prior to transferring if you have set special permissions on certain database objects.

The wizard will take care of the rest.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The easiest way to accomplish this is to use sp_detach_db on the source DB and sp_attach_db on the target DB. Check out Books On Line for help on exact syntax and usage.

good luck.
Be careful, DTS has a tendency to mess up things sometimes.

It wouldn't be that difficult to just stop the service on the old machine, copy the LDF & MDF files to the new machine and sp_attach_db on them....

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eugene ZCommented:
Restore backup on new box...
jdlsmithAuthor Commented:
Each of the above requires doing the process with EACH database individually... is there a way to do multiple DBs at once?
There is no way to do what you want to all 30 db's with just a few clicks or drag and drop.

The best I can offer is that you make a script to attach the db's on the new server. You can have all 30 re-attached in a few seconds of running the script. Of course you would still have to copy/move all 30 mdf & ldf files to the new server first and make a script.

example script:

EXEC sp_attach_db @dbname = N'pubs',
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

EXEC sp_attach_db @dbname = N'my_db',
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_db.mdf',
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_db_log.ldf'
If you us sp_detach_db then you're gonna be in a world of hurt. All users are stored in the master database. If you just detach and re-attach the DB's then you are going to have a huge problem of setting up all those users...

So to go over the things that are missed here:

MASTER DATABASE = Users and logins!!!
MSDB DATABASE = Scheduled Jobs & DTS Packages!!!

I'd say try the DTS but dont forget that you have your DTS packages and scheduled jobs too... If the DTS doesnt work then you should make a backup of each DB and restore them all to the new server.
You can run a stored proc to create logins from detached DB users--that's not a problem....

I've just had problems with DTS messing up primary and foreign keys and also changing identity fields to plain integer fields....

jdlsmithAuthor Commented:
One at a time it is!  Over all, I think it will be faster than manually copying files and restoring or attaching/detaching.

Thanks for the help!

Eugene ZCommented:
If you have ~30 DBs with size more then "pubs"
The best solution is: to restore the DBs from tape.
If there is not a way to backup on a tape - then DTS,etc.
EugeneZ... Fastest way is never to restore from tape if you have enough disk space to hold your bak files and the data.

I have my SQL Server setup like this:

4GB C:\ Drive for OS only.
16GB E:\ Drive for backups and swap file.

50GB F:\ Drive for data and logs. (one day I'd like to separate the logs from the data but right now there is no bottle neck on the drives so...)

In this setup if you have a couple small 2-5GB databases then you can store a few nights worth of backups on the "backup" drive and backup the entire drive to tape every night. Then if you have a problem you have a very quick backup to restore from. You're talking the difference between 5 minutes of down time and 45-60 minutes.

Have fun!
Eugene ZCommented:
Q. was "... I need to move/copy to the new machine.  What's the best way to do this?
Your solution is not good for "move/copy to the new machine" and for DR.
But for some cases it can be used like additional backup to a tape or another, then same box,  place backup
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.