Best utility(ies) to bulk port data between databases.

[SantaMufasa]

I have been tasked by one of our product managers to identify one or more utilities/scripts/methods that provide the best all-around bulk transmission of entire databases (free of collation definitions, et cetera) from a SQL Server database:

1) to another SQL Server instance,
2) to a non-SQL Server (e.g., Oracle, MySQL, et cetera) instance.

Amongst the home-brew methods that we are considering are scripts that create flat ASCII files constaining "CREATE TABLE..." and "INSERT..." statements.

What do you recommend?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[AlexCuse]

I would recommend using SQL Server's built in ETL tools to do this. DTS (2000) or SSIS (2005). You can create databases and tables using the 'Execute SQL Task' (I know it works on SQL Server, and it will probably work on Oracle and other connections as well, as long as the code is compatible with (x) dialect. You could then use Data Pump/Data Flow tasks to move the data from server x to server y.

There will be a bit of a learning curve involved with this though.

If you don't know the destination, the scripts will be the way to go, but if the tables are big I would consider sending the data itself in CSV files and loading them using Bulk Insert (this could be in another script)

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

DTS / SSIS or BCP.

BCP will only work if the remote machine is SQL Server, or if the other database software as a loader which can read the file that BCP outputs (it can output a CSV or other delimated file).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com