Export from MS SQL .mdf file to Oracle

Hello, everyone. I just finished this hellish task. I hope it will be useful for someone, and I don’t want to forget it myself :)

In this article I will show how to

  1. Import .mdf file to MS SQL
  2. Export from MS SQL 2008 to MS SQL 2005
  3. Export from MS SQL 2005 to Oracle.

Several little warnings. Those are not obvious because oracle & Microsoft don’t boast about it - I wasted much time to find it out.

  1. You can’t export directly from MS SQL 2008 to Oracle.
  2. You can’t export directly .mdf file to Oracle.
  3. MS SQL 2005 Express Edition works on Windows XP 2. Full version doesn’t.
  4. MS SQL Management Studio can do export to Oracle. Theoretically. On practice, it can’t.

So, let’s begin.

1) Import .mdf file.

The simplest way is to launch MS SQL Management Studio, set up connection to database and right click on "Databases" folder inside the connection tree. After it, choose "restore database", click "from device" and choose .mdb file. Everything should go fine.

2) Export from MS SQL 2008 to MS SQL 2005

You must have MS SQL 2008 and 2005 server installed for my method. Launch MS SQL Management Studio, connect both MS SQL 2008 and 2005, and right click on database you need to export. Choose "Tasks > Export data", and proceed, everything should be obvious at this moment.

3) Export from MS SQL 2005 to Oracle

You should have MS SQL 2005 installed. Also, you need **Oracle SQL Developer** tool (can be downloaded from Oracle web site) with extension "**JTDC JDBC driver**" (Can be downloaded from Oracle SQL Developer interface after installation). Your MS SQL server should be configured to use both Windows & SQL authenification and your "sa" account should be configured (Windows authenification does not work with Oracle SQL Developer). Also, you need MS SQL server to be configured to accept TCP/IP connections.

If you can’t launch MS SQL configure tool (“Cannot Connect to WMI Provider. You do not have permissions of the server is unreachable” error), you can go to registry to

HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/_(your server internal name, for example, MSSQL.1)_/MSSQLServer/SuperSocketNetLib/Tcp
and configure it manually (Set up "enable" as "1" and ports).

If you met all those requirements, launch Oracle SQL Developer, set up connection to Oracle and MS SQL, click on MS SQL connection and select “Quick migrate MS SQL Server”. After it, everything is also obvious.

Helpful links from Oracle: Download Oracle SQL Developer. Getting started with Sql Developer Migrations.