By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Backing online database table data to local machine

P: n/a
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 Morgan
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
John Morgan (jf*@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.