Q

How can I back up my SQL Server DB on Oracle 9i?

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?
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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close