connection to local remote system from web

Summary for @Deepa: two practical approaches work when a web app must copy a SQL Server database from a machine at location A: (A) create a server-side snapshot (.bak) and transfer that file, or (B) stream data table-by-table into the target using a data copy routine. Exposing the SQL port directly to the Internet is risky—prefer a VPN/SSH tunnel or a small authenticated service on the DB host that produces and delivers backups.

Snapshot approach (full DB). Run a server-side BACKUP DATABASE to a path on the database machine, then move the .bak via SFTP, HTTPS, or a secured file share. Important: the BACKUP path is on the DB server, not the web client. Example .NET code to trigger a backup remotely:

using System.Data.SqlClient;

string conn = "Server=remoteHost,1433;Database=master;User Id=myUser;Password=myPass;";
using (var cn = new SqlConnection(conn))
{
    cn.Open();
    string tsql = @"BACKUP DATABASE [MyDb] TO DISK = N'C:\Backups\MyDb.bak' WITH INIT";
    using (var cmd = new SqlCommand(tsql, cn)) cmd.ExecuteNonQuery();
}

Programmatic copy (table-level). For selective or incremental copies, stream rows from the remote DB and write them to the destination with SqlBulkCopy. This avoids moving a full .bak when only some tables are needed:

using (var src = new SqlConnection(srcConn))
using (var dst = new SqlConnection(dstConn))
{
    src.Open(); dst.Open();
    using (var cmd = new SqlCommand("SELECT * FROM dbo.Table", src))
    using (var rdr = cmd.ExecuteReader())
    using (var bulk = new SqlBulkCopy(dst)) { bulk.DestinationTableName = "dbo.Table"; bulk.WriteToServer(rdr); }
}

Notes, security and troubleshooting: @amitlokare’s suggestion to run a small service on the DB host is practical—have that service authenticate requests and push backups out rather than opening SQL to the net. @campkev’s DSN idea can work inside a trusted LAN, but connection strings are simpler for web apps. Common pitfalls: TCP/IP disabled in SQL config, firewall/port blocking (named instances need SQL Browser or explicit port), insufficient SQL permissions for BACKUP, and disk space for .bak files.