Copy table from one SQL Server to Another

Using ASP.NET code (VB) I need to copy tables from one SQ: Server to another
SQL Server. This is a poor mans safety backup of some critical data. The
source SQL Server is hosted by an ISP where there is no way to access the DB
via EM or from anywhere other than web pages on that ISP web account so DTS
and such are not options.
WayneIf you're going "poor man's" route ...
Setup the source server as a linked server via your local SQL Server EM
using the credentials you use to login. Then while in your local DB, run
something like
select *
into [My Backup]
from [sqlServer.ispName.com].databasename.dbo.table
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to anoth
er
> SQL Server. This is a poor mans safety backup of some critical data. The
> source SQL Server is hosted by an ISP where there is no way to access the
DB
> via EM or from anywhere other than web pages on that ISP web account so DT
S
> and such are not options.
> Wayne
>
>|||The source server can only be "seen" from web pages on the ISP account!
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> If you're going "poor man's" route ...
> Setup the source server as a linked server via your local SQL Server EM
> using the credentials you use to login. Then while in your local DB, run
> something like
> select *
> into [My Backup]
> from [sqlServer.ispName.com].databasename.dbo.table
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
another
the DB
DTS|||Wayne Wengert wrote:
> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to
> another SQL Server. This is a poor mans safety backup of some
> critical data. The source SQL Server is hosted by an ISP where there
> is no way to access the DB via EM or from anywhere other than web
> pages on that ISP web account so DTS and such are not options.
> Wayne
I'm not sure how you can do that. You would probably need access to the
"other" SQL Server from the ISP's SQL Server by adding a linked server,
but I doubt you have those rights based on what you wrote. You could
create an ASP.Net page to display the contents of the table in a
datagrid (control name?), but from there all you have is the data in an
HTML page. I suppose you could package the table as XML and return the
raw XML in the HTML results.
Are you saying that the ISP won't provide you a backup of the database?
It's strange that you have ADO.Net access to the SQL Server from
ASP.Net, but can't access the server using something like the web data
administrator or even Query Analyzer. I guess the SQL Server has no
direct internet access and is behind the web server (which is a secure
way of setting things up). It's just more difficult from a maintenance
standpoint.
Something tells me there is more harmonious solution here... that I'm
probably just not thinking of. I would request from the ISP they give
you a backup of the database.
David Gugick
Imceda Software
www.imceda.com|||Since this is "critical data" ... get a new ISP ;-)
What about creating a page that outputs CSV or XML and then writing a DTS
package (on local) to grab the data from the URL?
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> The source server can only be "seen" from web pages on the ISP account!
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> another
> the DB
> DTS
>
>|||Alex;
Thanks for the response. I don't get to choose the ISP! I know that if I
have the table structure on the backup server I can write code to read each
row form the source server and "Insert Into" the backup copy. My problem is
that if they add or remove a field from a table on the source server I need
to reflect that change?
I guess that what I am looking for is a way to create the "CREATE TABLE..."
syntax in code?
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> Since this is "critical data" ... get a new ISP ;-)
> What about creating a page that outputs CSV or XML and then writing a DTS
> package (on local) to grab the data from the URL?
>
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
EM
run
The
access
so|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esSLUXFJFHA.2884@.tk2msftngp13.phx.gbl...
> Wayne Wengert wrote:
> I'm not sure how you can do that. You would probably need access to the
> "other" SQL Server from the ISP's SQL Server by adding a linked server,
> but I doubt you have those rights based on what you wrote. You could
> create an ASP.Net page to display the contents of the table in a
> datagrid (control name?), but from there all you have is the data in an
> HTML page. I suppose you could package the table as XML and return the
> raw XML in the HTML results.
> Are you saying that the ISP won't provide you a backup of the database?
The ISP does nightly backups but their restore services don't give me a warm
fuzzy (-;
I also want to get copies of the tables into an environment where I can use
other tools to see what is going on. The only export they offer is a CSV of
the data rows (no way to export the table structure I see.)

> It's strange that you have ADO.Net access to the SQL Server from
> ASP.Net, but can't access the server using something like the web data
> administrator or even Query Analyzer. I guess the SQL Server has no
> direct internet access and is behind the web server (which is a secure
> way of setting things up). It's just more difficult from a maintenance
> standpoint.
Amen to that!!!!!
> Something tells me there is more harmonious solution here... that I'm
> probably just not thinking of. I would request from the ISP they give
> you a backup of the database.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Oy.
Well, looks like you're just going to have to write it yourself. DDL isn't
very hard (I create my tables like that, instead of using EM). And then
you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
Some quick queries ...
select table_name from information_schema.tables
select column_name, data_type, data_size from information_schema.columns
where table_name = ?
Look thru the results, build your CREATE TABLE statement.
Good luck!
-- Alex
"Wayne Wengert" wrote:

> Alex;
> Thanks for the response. I don't get to choose the ISP! I know that if I
> have the table structure on the backup server I can write code to read eac
h
> row form the source server and "Insert Into" the backup copy. My problem i
s
> that if they add or remove a field from a table on the source server I nee
d
> to reflect that change?
> I guess that what I am looking for is a way to create the "CREATE TABLE...
"
> syntax in code?
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> EM
> run
> The
> access
> so
>
>|||Thanks for the suggestion and the sample queries. I'll give it a try.
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account|||Bummer... they don't allow access to "master" SP's. On to Plan C
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account