Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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.

Thanks,

JS
0
jdlsmith
Asked:
jdlsmith
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Frostbyte_ZeroCommented:
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
 
brettcCommented:
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
 
arbertCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Eugene ZCommented:
Restore backup on new box...
0
 
jdlsmithAuthor Commented:
Each of the above requires doing the process with EACH database individually... is there a way to do multiple DBs at once?
0
 
brettcCommented:
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
 
SQLDBACommented:
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
 
arbertCommented:
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
 
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!

JS
0
 
Eugene ZCommented:
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
 
SQLDBACommented:
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
 
Eugene ZCommented:
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

Independent Software Vendors: 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!

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now