SQL Server data migrations go through client machine or not?

Question

I want to move a lot of data from a SQL Server database on one machine to another machine. Both machines are remote. If I run the Copy Database wizard, will it pull all the data across the Internet to my client machine and then push it back out to the destination? Or will the source and destination machines communicate directly?

It's a very big database, so this matters quite a bit.

One complexity is that my login only shows a view of a subset of the data, so backup and restore would not work. I have to copy from a view to a table.

I can set things up so the two remote machines can see each other. I don't think I would be able to run the wizard on one of them, though, because I all I have is a login to the db and no other access to the OS.

Answer 1

If you run the copy database wizard then yes all the data will go through which ever machine you run the copy database wizard on.

If you are moving a large database, especially one with a complex schema then backup and restore is a much better option.

Answer 2

Perhaps you could create a linked server on the target server, and then use an INSERT INTO... SELECT query to copy the data from the linked server directly into the target table.

Something similar to this might work:

EXEC master.dbo.sp_addlinkedserver @server = N'SourceServerName', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SourceServerName', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SourceServerName', @locallogin = NULL , @useself = N'True', @rmtuser=NULL,@rmtpassword=NULL

INSERT INTO TargetTable (FieldOne, FieldTwo, ...)
SELECT FieldOne, FieldTwo, ...
FROM SourceServerName.DatabaseName.dbo.SourceTable;

Answer 3

You could use BCP on one server is you have RDP access.

bcp database.schema.view out c:\temp\file.dat -S remoteserver -T -c

Then load it with

bcp database.schema.table IN c:\temp\file.dat -c -T 

If you do not have RDP access but do have xp_cmdshell then you can execute the statements with T-sql.

exec xp_cmdshell ' bcp database.schema.view out c:\temp\file.dat -S remoteserver -U username -P password -c'
exec xp_cmdshell 'bcp database.schema.table IN c:\temp\file.dat -c -T'