0

Is it possible to run the SQL Server Import/Export Wizard purely on a remote server (e.g., from one database to another on the same remote server)?

If I setup the source/destination connections in the Wizard on my local machine, I can only enter connections to the remote server as I would make them from my local machine. I cannot enter the connections as the remote server would make them. If I then run the Wizard, it transfers the data via my local machine, even though it would be much faster if the remote server connected directly to the source/destination.

For example:

  • A single SQL Server on a remote machine with two databases: DB1 and DB2.
  • SSMS on local machine connected to SQL Server on remote machine.
  • Task: copy (very large) table from DB1 to DB2.
  • Setup Import/Export Wizard with connections to both tables.
  • Issue: data transfer via local machine (from DB1 to local machine to DB2).

I have looked at the Wizard documentation, but I couldn’t find any description of this issue. Am I missing something? I would guess that most SQL Servers run on some remote machine and not locally.

Improve this question

1 Answer 1

Reset to default
1

The code executes on the machine where you run the wizard. So, no, the GUI will not allow, by itself, a "remote execution".

There are other ways to go about this. Being on the same server, I would open a query window and do SELECT INTO. That is likely the fastest way to achieve what you want. It don't allow for breaking up into several transactions, though.

You can of course RDP to the server and run the wizard on the server, but I wouldn't do that.

And you can of course export the data to a file (using for instance a GUI ot BCP) and then import from that fil (using for instance BCP, BULK INSERT or a GUI) but again that is far more messy than any of the pure T-SQL solutions.

Another option is to create the destination table first and then do INSERT ... SELECT. That can allow for several transactions, if you do several of those INSERT ... SELECT ... (with some suitable WHERE clause so that each SELECT cover different sets of the data you want).

Improve this answer
2
  • Thanks for clarifying. Is there any use-case based rational for that? Most other functions in SSMS are executed server-side. For example, if I run a backup, it runs on the server and not via my local machine. If I rebuild an index, it runs on the server and not via my local machine. It seems to make very little sense to have such a fast and powerful tool to be limited to local execution.
    – Phil C
    Commented Jun 27 at 10:53
  • The reason is architectural. The wizard is an exe file, not a T-SQL command. One could argue that Ms build support for remote execution of exe files - that would be a product enhancement request. :-)
    – Tibor Karaszi
    Commented Jun 27 at 14:36

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