moving data between servers

[Arun]

Hi,

I have a query on the best way for moving data across the SQL Servers. We
have a concept of "Projects" in our tool and the data is organized under
these "Projects". We have a requirement for export a project's data to a
file and import that back (in a different machine, where our tool is
running). This data is present in multiple tables. I'm planning to fetch all
the data in a dataset and use it's WriteXML method to generate an XML file.
At the other end (for import), generate the DataSet from the XML file,
retrieve the data from dataset tables and populate in the SQL Server tables.
I'm just wondering if this is an efficient way of achieving the
export/import of my project data or if there is anyother better way of doing
this..

Thanks in advance,
Arun

 

[GrantMagic]

Why don't you use Sql Server's built in backup and restore feature?

 

[RJ]

The SQL BulkCopy feature is specifically designed for fast export of data.
There is also a command line utility that does both import / export to file,
named bcp.( bulk copy program)
See the SQL docs.

 

[Arun]

As I 've told you, I have to fetch the data that belong to a particular
"Project". I need to filter out the data depending on the project ID and I
have to invoke this programatically. That's the reason I went for "custom"
select queries.

My plan is to start from the "Projects" table and find out which all tables
are having a "foreign key" relationship with this table and for these
tables, I'll find out all the tables that these tables depend in turn and
include their data (and so on. it goes recursively till all the
"dependencies" are resolved).

I have come across the sp_fkeys and sp_pkeys system procedures that returns
the foreign key and the foreign key table name for a given table. But I
don't know how process the results of one stored procedure's output inside
another stored procedure. Basically i have to fetch these results and inturn
go and find out the dependencies of the tables returned in the result. Any
help would be helping me a lot.

thanks,
arun

 

[Arun]

Hi,

thanks for the reply. My requirement is slightly different, can you please
refer to my reply to the first response and check if you can help me out??

Arun