I have a Windows SQL Server 7.0 database, but I want to back up the content of this database and restore it on my Oracle 9i database. How can I do this?

    Requires Free Membership to View

    Login

If I had data in SQL Server and I wanted a backup of that data, I'd create a SQL Server backup. Similarly, I'd use an Oracle backup for my Oracle databases. The two do not mix and match. However, you may have other requirements that were not outlined in your question.

Since Oracle and SQL Server are two completely different products, the only way to move data from one to the other is to use an intermediary step. One of the most vendor-neutral steps is to dump your data from SQL Server to comma-delimited text files, with each file representing one database table. You can then use Oracle's SQL*Loader utility to ingest the contents of these files into Oracle database tables. If you have many tables, this can be a cumbersome solution.

Another intermediary step is to move the data through an ODBC connection. Create an ODBC connection to an Oracle database using Oracle's ODBC drivers. Then have SQL Server's Data Transformation Services (DTS) move the data to the database defined by the ODBC DSN. Alternatively, you can set up Oracle's Heterogeneous Services (HS) to connect the Oracle database to SQL Server through ODBC and then access the SQL Server data through a database link.

This was first published in May 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top