Solved

copy table data from remote sql server to local sql server

Posted on 2008-10-26
10
453 Views
Last Modified: 2012-06-27
I need to move some updated table data from a table on a remote sql server to my local development instance of sql server.

Both the remote sql server and my local version are running Sql Server Management Studio Express 2005.

I'm not able to register the remote server locally because of an issue with the firewall and VPN connection.  Our network admins are working on that issue.

So, in the meantime, I need to copy some table data from the remote sql server to my local sql server.

Is BCP the best way to accomplish this?

Export using BCP on the remote server to a comma delimited file, truncate the table on my local dev environment and import using BCP?
0
Comment
Question by:-Dman100-
[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
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22808338
You can use BCP, but I would do native format not character format.  The difference is in the use of the -n instead of the -c command line option.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808394
you can also have a choice of  Microsoft Data publishing tool for sql server

Download this freel tool by Microsoft for publishing Data
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Then
in Wizard if you just want to generate data insert statements
set the following options

uncheck Script all object
Next
Schema qualify= False
Types of data to publish =Data only

end result is data script which you can execute on another Database

The tool can also generate scripts for other objects in the database ie. Tables

Hope that helps
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22808427
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:-Dman100-
ID: 22808628
I tried using the Microsoft Publishing tool, but got the following error:

TITLE: Microsoft SQL Server
------------------------------

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Database+Publishing+Wizard&ProdVer=1.1.1.0&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808661
try reinstalling
0
 

Author Comment

by:-Dman100-
ID: 22808679
Do I need to install the following:
Microsoft SQL Server 2005 Management Objects Collection (I'm not sure if this is installed already)

I have a x64 system.  Would this cause a problem?
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808739
i use this tool everyday without any crash so can't really give you the exact answer about the reason of this exception but i do have SQL SERVER Managment studio installed in my environment both on remote SQL SERVERS and Local
but you can check the Known Issues section here
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=DPW%20Known%20Issues&referringTitle=Database%20Publishing%20Wizard
0
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
ID: 22808763
also the tool is installed on  XP and Vista 32 bit systems in my environment
0
 

Author Comment

by:-Dman100-
ID: 22808843
Got it working.  Thank you.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808861
Glad your problem resolved
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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