Solved

Best way to move data from one server to another

Posted on 2013-01-02
7
333 Views
Last Modified: 2013-01-02
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
0
Comment
Question by:cipriano555
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Yagya Shree
ID: 38738605
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
0
 
LVL 13

Expert Comment

by:Yagya Shree
ID: 38738609
Export  and import table data into a excel file using DTS or SSIS package
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38738632
From SSMS, expand Databases, Right click on the database you want to import into, select tasks from the context menu and then import data...

From there you can specify server and DB from, server and DB to and the table(s) you want to copy.

Click through, click finish and done - it will do it all for you (make sure the target table is empty before you start though).
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:cipriano555
ID: 38738875
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.
0
 

Author Comment

by:cipriano555
ID: 38738889
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.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38739029
I'm unclear from your last comment, did the import using the import/export wizard work ?
0
 

Author Closing Comment

by:cipriano555
ID: 38739059
You're a genius!  Worked like a charm and I didn't have to deal with transfering files.

John
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now