In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Solved
Best way to move data from one server to another
Posted on 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.Tabl
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
SELECT * FROM machine_A.DB_Name.DBO.Tabl
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2
7 Comments
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
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
Active 5 days ago
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).
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).
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.
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.
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.
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Course of the Month7 days, 12 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.