Exporting table data to an external server

Posted on 2011-04-20
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?
Question by:vcbertini
    LVL 1

    Expert Comment

    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 Comment

    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.
    LVL 16

    Accepted Solution

    This thing is technically called as Replication,  please follow the link for other details:
    LVL 21

    Expert Comment

    by:Alpesh Patel
    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 Comment

    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?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    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…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now