Solved

copy table data from remote sql server to local sql server

Posted on 2008-10-26
10
452 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-
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to import SQL 2000 database to SQL 2014 5 162
Replace Dates in query 14 56
Query to return total 6 25
SQL parsing XML works but want to do it another way 4 23
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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