Backing online database table data to local machine

I have an online SQL Server database provided by an ISP. I do not have
permission to create a backup device and I understand this is normal
practice.

I am not using Enterprise Manager to administer the online database.

I know I can back up the structure of the database using SQL
scripts.

My question is:

How do I back up on my own machine the data contained in the online
database tables I have created? If I were using Enterprise Manager I
could do it by downloading tables using the DTS facility but how can I
do it without Enterprise Manager?

Is there some work around which I have missed eg creating a csv file
of the data?

Best wishes for 2005 to all those helpful people in this newsgroup!
John MorganJohn Morgan (jfm@.XXwoodlander.co.uk) writes:
> I have an online SQL Server database provided by an ISP. I do not have
> permission to create a backup device and I understand this is normal
> practice.
> I am not using Enterprise Manager to administer the online database.
> I know I can back up the structure of the database using SQL
> scripts.
> My question is:
> How do I back up on my own machine the data contained in the online
> database tables I have created? If I were using Enterprise Manager I
> could do it by downloading tables using the DTS facility but how can I
> do it without Enterprise Manager?

You can take a proper backup from to your own machine, as the SQL
Server machine is not likely to have access to your disks, and anyway
I don't think network backups are not supported. But you seem to understand
that anyway, since you mention DTS.

DTS can be run with dtsrun from the command-line, but I assume that
you need Enterprise Manager to create the packages. Then again, I don't
know DTS, so don't take it from me.

But there is BCP. Run this query:

SELECT 'BCP ' + db_name() + '..' + name + ' out ' + name + '.bcp ' +
'-S Server -n -U usr -P pw'
FROM sysobjects
WHERE type = 'U'
AND objectproperty(id, 'IsMSShipped') = 0

The cut and paste that result into a BAT file that you run.

Here I have specified -n which means native format. You can use -c instead
to get character format, with tab delimiting columns and newline delimiting
lines. You can also specify other delimiters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp