INF: How Scheduled Data Transfer Works (172012)

SUMMARY

SQL Enterprise Manager includes a transfer management interface that you can use to transfer some or all objects from one database to another. You can start the transfer immediately, or you can schedule it to occur either once or on a recurring basis.

Scheduled data transfer is a two-step process:

Step 1: A CmdExec task is inserted into destination server's msdb..systasks table. This task invokes SQL Server Transfer Agent, Xfragent.exe.

The task also contains information about the directory path where all transfer scripts are stored. This path is specified in the "Save Transfer Files in Directory" entry, and it refers to a location on the destination server.

Step 2: When the task executes on the destination server at the scheduled time, it reads transfer scripts that were stored on the destination server's local directory, and completes the actual data transfer.

MORE INFORMATION

The corresponding task will always be inserted into the destination server's msdb..systasks table, whether the transfer is administered using SQL Enterprise Manager from the source server or destination server.

The "Save Transfer Files In Directory" refers to location on the destination server, even if the transfer is initiated on the source server. Normally, if the directory does not already exist, SQL Enterprise Manager creates it. However, if the specified drive cannot be found on the destination server, the task will fail to run, and the task history will indicate a "Process Exit Code 1, Invalid Transfer Log Director" error.