No you read the documentation correclty but what it doesn't say is that it
routes the transfer through your machine.So say you have a 40 mb table you
wish to transfer then this is going to take time to bcp between the servers
especially if both servers are remote and connected by say a 64kb ISDN.
What I was trying to suggest is that you script as normal but your data
transfer is done by BCP'ing out to the local drive on the server zip and
copy the file to the desination server and unzip and bcp in locally to that
machine so there is no BCP'ing over a slow link. This is the fastest way to
do this kind of transfer. I have done that here across a 100 site before in
a night where as transfer manager (which I tried first) took forever to do
a single site.
>Maybe I read the documentation incorrectly, but I thought that the Transfer
>Manager does exactly what you just said. The Transfer Manager reads from
>the syscomments table to recreate the objects on the new server. It then
>uses bcp to move the data through. Did I misunderstand something?
>Rick Sawtell MCP, MCT, MCSD
>10 Million Lemmings Can't Be Wrong
>>I agree with you on the first way but the problem with the second one is
>>transfer manager works like the copy command under windows by the fact
>>the data is transfered via the pc running the copy/transfer manager.
>>If this is the server then that is better but if the guy is competent in
>>server I would recommend he script the whole database (using the script
>>generator) then write a script to bcp the data out from all tables to the
>>local hard drive. Zip all the files up and copy them to the receiving
>>server (if both are remote servers do the bcp,zip and copy via
>>Then on the receiving server unzip the datafiles using xp_cmdshell and bcp
>>them back in, again using xp_cmdshell,. This is the fastest way to copy
>>data. And potentially the only feasible way if the database is very
>>One thing to remeber though is the account used to start the MSSQLServer
>>service will need the permissions to get to the hard disk and copy data
>>accross the network.
>>>Your best bet is to install SQL Server 6.5 on the second machine. When
>>>has successfully finished it's installation, use the Enterprise Manager
>>>register both the old and new SQL Servers. Now you have two choices.
>>>1. If you have the same character set and sort order, then you can
>>>the databases from the old server and restore them on the new server.
>>>sure to create the appropriate devices first.)
>>>2. If you have different character sets or sort orders, then you can
>>>the devices and databases on the new server and then use the Transfer
>>>Manager to move data, schema and objects from the old to the new.
>>>Rick Sawtell MCP, MCT, MCSD
>>>10 Million Lemmings Can't Be Wrong
>>>>Hi, i got some sql database installed on a sql server 6.5 with win NT
>>>>i'm doing a parrallel installation of the winNT server on a new machine,
>>>>evrey thing is doing fine except the fact that ho do i do to
>>>>migrating sql sever 6.5 from sql server 6.5 to another sql server 6.5
>>>>that's the big problem
>>>>for your help