Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to migrate a SQL Server db 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.

share|improve this question
2  
if it really is a big database you probably should do a backup and restore of the db instead of copying it with the wizard. –  Max Vernon Apr 3 '13 at 0:25
    
Understood. One complexity, though, is that my login only shows a view of a subset of the data, so a backup may not work. I have to copy from a view to a table. –  ccleve Apr 3 '13 at 0:28
1  
Both machines are remote - ok, but can they see each other? If so, is the connection between them robust? Or is one in Chicago and the other in North Korea behind a very stringent firewall? –  Aaron Bertrand Apr 3 '13 at 2:08
    
I can set it up so they 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. –  ccleve Apr 3 '13 at 15:57

2 Answers 2

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.

share|improve this answer
    
Thanks. See my comment about views above. –  ccleve Apr 3 '13 at 0:29

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.