?
Solved

Copy SQL DBs from one machine to another

Posted on 2003-03-13
12
Medium Priority
?
175 Views
Last Modified: 2012-06-27
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.

Thanks,

JS
0
Comment
Question by:jdlsmith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 3

Accepted Solution

by:
Frostbyte_Zero earned 300 total points
ID: 8130721
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.
0
 
LVL 1

Expert Comment

by:brettc
ID: 8130744
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8130757
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....

Brett
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 8130964
Restore backup on new box...
0
 
LVL 1

Author Comment

by:jdlsmith
ID: 8131017
Each of the above requires doing the process with EACH database individually... is there a way to do multiple DBs at once?
0
 
LVL 1

Expert Comment

by:brettc
ID: 8131103
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'
0
 
LVL 1

Expert Comment

by:SQLDBA
ID: 8131710
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8131733
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....

Brett
0
 
LVL 1

Author Comment

by:jdlsmith
ID: 8131890
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!

JS
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8135470
P.S.
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.
0
 
LVL 1

Expert Comment

by:SQLDBA
ID: 8146941
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:

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

RAID ARRAY 2:
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!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8148643
SQLDBA...
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question