Avatar of cipriano555
cipriano555
 asked on

Best way to move data from one server to another

I need to make a copy of a data table residing on one server to a table on a different server.  I got into a session on machine_b and tried something like this:

SELECT * FROM machine_A.DB_Name.DBO.Table_name

But got this error message

Msg 7202, Level 11, State 2, Line 1
Could not find server 'machine_A' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

The table is not huge,  If I could script out the data, I could copy and past the insert statements and that would work, but when I script the table, I just get its definition, not the data.  I'm in SQL Server 2005.  Any suggestions?

Thanks,

John
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
cipriano555

8/22/2022 - Mon
Yagya Shree

Create new table
move data of parent table to new table
create a new file group in existing table
rebuild cluster index or create primary key into new table [in that case your new table data will move to new file group, which you have recently created]
take a file group backup
restore it on your 2nd sql instance
Yagya Shree

Export  and import table data into a excel file using DTS or SSIS package
ASKER CERTIFIED SOLUTION
Steve Wales

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cipriano555

ASKER
to yagyashree: my question is, how do you move that data?  I made a backup of the source database...but the backup goes to the source server....target server can't get to it (I don't know how to make it see it). So that won't do it....query that gives full path including server doesn't work (see error message in my post).

Yep, I can export the data to excel on the source machine...I think my question is more about getting data transfered from one machine to another than it is a database question...

To: sjwales I'll try that right now and see if I can do it.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cipriano555

ASKER
BTW, I'm getting into SSMS.  There I have connections for the two servers, which I use to get to and work with databases on each.  I don't know how access the directory structures so that I could email export files to move them.  I know this is a really basic point to get stuck on, but I don't know how to even get into Windows Explorer and locate the file.  If I use windows explorer, I just see my local machine, I don't see either of the machines where the databases are located...I don't know how to find them.
Steve Wales

I'm unclear from your last comment, did the import using the import/export wizard work ?
cipriano555

ASKER
You're a genius!  Worked like a charm and I didn't have to deal with transfering files.

John
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.