We help IT Professionals succeed at work.

Exporting table data to an external server

230 Views
Last Modified: 2012-05-11
I am looking for a simple way to export four tables from our internal Sql Server 2005 database ("Server A") to an external SQL Server 2005 database ("Server B").   Server A is hosted locally and Server B is hosted away from our location. I usually access it via Remote desktop.  This purpose of this export is for archival processes.

1. Is this even possible?
2. Is this easy to do?
3. Is this practical?  (or is there a better suggestion from getting the data from Server A to Server B)

Up to this point I have been doing a backup on Server A, emailing the backup to myself, then doing a restore on Server B. This seems inefficient. Suggestions?
Comment
Watch Question

Commented:
If there is sufficient connectivity, building a SSIS package between the two servers(i.e., VPN, etc, allowing Server A to connect to Server B and vice versa) approach.  Create a Publication on Server A, then build a corresponding subscription on Server B. alternatively, you could also link the two servers(requiires similiar connectivity as before) and have a scheduled script to programmatically pull the data from A to B.  

Author

Commented:
It might be easier to push the data since Server A is behind a firewall. What kind of connectivity are you referring to? Is this FTP or ODBC or something like that? I'm kind of new to SSIS packages.
Consultant Software Engineer - .NET Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Hey, if your external server has live IP then you connect it at your desktop SSMS and use import/export wizard to pull or push the data here and from.

Author

Commented:
I'm not exactly sure what our external server has since it is a hosted server. Is there a standard connectivity method that one can use from one server to another? I have a bunch of databases, stored procedures and tables to move over (i guess "replicate"), and I can't even get the servers to talk to one another. How do I get them to connect?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.