transfer database between two servers which don't want to talk

5 Jun 2006 3:35 PM

Alan Baljeu

All I want is to trasfer the data in one SQL server 2000 installation to
another. Unfortunately, I can't get them to talk due to these computers
existing in different domains, and assorted related obstacles. I can
however transfer files between the computers.

I need to copy tables, and views. I would like to copy roles and stored
procedures, but I can rebuild those if absolutely necessary. Can anyone
help?

5 Jun 2006 5:31 PM

Helmut Woess

Am Mon, 5 Jun 2006 11:35:32 -0400 schrieb Alan Baljeu:

> All I want is to trasfer the data in one SQL server 2000 installation to
> another. Unfortunately, I can't get them to talk due to these computers
> existing in different domains, and assorted related obstacles. I can
> however transfer files between the computers.
>
> I need to copy tables, and views. I would like to copy roles and stored
> procedures, but I can rebuild those if absolutely necessary. Can anyone
> help?

Data, stored procs, views, trigger, all this you have in a backup. Make a
full backup and send this file per FTP, mail whatever you want to the other
computer. On the target computer create a new empty database and make a
restore, using this backup. What you have to do by hand is the security
part (login, grants, ..). Easiest way is doing all with Enterprise Manager.

bye, Helmut

5 Jun 2006 7:11 PM

Alan Baljeu

>> I need to copy tables, and views. I would like to copy roles and stored
>> procedures, but I can rebuild those if absolutely necessary. Can anyone
>> help?
>
> Data, stored procs, views, trigger, all this you have in a backup. Make a
> full backup and send this file per FTP, mail whatever you want to the
> other
> computer. On the target computer create a new empty database and make a
> restore, using this backup. What you have to do by hand is the security
> part (login, grants, ..). Easiest way is doing all with Enterprise
> Manager.

Thanks for the suggestion. After investigating this, I found an even easier
approach: I took the database offline, copied the database files, and then
brought the copies online at the new computer. This works perfectly.

5 Jun 2006 9:56 PM

Erland Sommarskog

Alan Baljeu (alanb@cornerstoneSPAMBLOCKmold.com) writes:

> Thanks for the suggestion. After investigating this, I found an even
> easier approach: I took the database offline, copied the database files,
> and then brought the copies online at the new computer. This works
> perfectly.

Note that it is likely that user-login mappings gets out of whack when
you do this. You can use sp_change_users_login to adress this. See
Books Online for details.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

6 Jun 2006 8:29 AM

Hank Arnold

There are two advantages to using the backup/restore process. The first
is that the backup process (using SQL Server Agent) does not require
taking the source database off line. You *will* have to take the target
database off line....

The second is that it's a smaller file and will transfer quicker....
Plus you have a good backup of the db.

Regards,
Hank Arnold

Alan Baljeu wrote:
Show quote

>>> I need to copy tables, and views. I would like to copy roles and stored
>>> procedures, but I can rebuild those if absolutely necessary. Can anyone
>>> help?
>> Data, stored procs, views, trigger, all this you have in a backup. Make a
>> full backup and send this file per FTP, mail whatever you want to the
>> other
>> computer. On the target computer create a new empty database and make a
>> restore, using this backup. What you have to do by hand is the security
>> part (login, grants, ..). Easiest way is doing all with Enterprise
>> Manager.
>
> Thanks for the suggestion. After investigating this, I found an even easier
> approach: I took the database offline, copied the database files, and then
> brought the copies online at the new computer. This works perfectly.
>
>