Welcome back to our series introducing you to Oracle’s SQL Developer application. Check out Part I, Part II and Part III. In this post we are going to cover how to backup your data using SQL Developer. It’s a simple process involving a couple of clicks, menu choices and small amounts of typing.
Backing up your database is an important task. With a proper backup of your data, you can feel confident in trying new concepts and statements with your database. Especially for those of us that “inherited” the duties of a Database Administrator (DBA), but were never trained as such.
As we stated in the previous posts, you will need to download Oracle’s SQL Developer application. It’s free, but you will have to create an Oracle.com account to actually download the software.
At the time of this post, the current version is 2.1 and is available at
The software is available for several types of platforms. Also, since it’s based on Java, there is a download version that includes the proper JDK. Once you uncompress the zip file, SQL Developer is ready to be used. Find the sqldeveloper.exe file and double click it to get started.
There are three terms we should review before getting started with our main objective. The terms are data migration, data copy and data export. Although, in casual conversation, all three terms can be used to reference backing up a database, each term has a specific purpose. These three terms do not mean the same thing. The following is a brief description of what each term facilitates.
Data Migration is used when you want to migrate your data from a non-Oracle database (MySQL, MS SQL Server, MS Access and Sybase are a few examples) to an Oracle database. Data Migration usually consists of a three to four part process known as capture, map and migrate. Migration is not the same as backing up your database. Migration is the act of moving your database from one database technology to another.
Data Copy is used when you want to create an exact copy of your database on the same server. For example, you can create a new connection to your Oracle database called “userdbbackup”. You could then connect with your normal SQL Developer connection and also connect with your new userdbbackup connection. Yes, you can have more than one connection open at a time.
Then you could use Data Copy to move EVERY THING from one connection to another. Essentially, Data Copy will copy one database to another database on the same server. The spirit of “backing up” is met with this action, but what if your server crashes? You could lose your database and the backup of your database at the same time.
Data Export is the action that we want to work with in this post. Data Export will create an in depth Data Definition Language (DDL) document that you can save somewhere other than your database server.
You can think of this Data Export DDL file as a “snap shot” of your database at the time it was created. In theory, you can copy/paste everything from the DDL file into an SQL Developer worksheet, click the Run Script button and recreate your database.
Performing a Data Export
First you want to open SQL Developer and connect to the database you want to export. Next, choose Data Export from the Tools menu. A graphical representation of our steps so far looks like this:
Upon choosing Database Export you will see the Export Wizard. As we describe each step, assume that we are using the Next button to transition to the next step.
Step 1 of the Export Wizard asks that you indicate the name of your export file and its save location. You also get several DDL options. For our purposes, we are sticking with the two default DDL options. Our Export Wizard step 1 looks like this:
Step 2 of the Export Wizard asks that you indicate which types of database objects you want to include in the export. Because we are methodical IT types (aka Obsessive Compulsive), we want to export every type of object. Our Step 2 looks like this:
Step 3 of the Export Wizard asks that you specify which members of each type that you want to include in the export. The quickest method to include every member of every type is to first click on the Go button.
This will produce a list of every member of every type. Second, click on the blue double arrow button that is pointing to the right. This will move every member of every type into the right side column. Our version of step 3 looks like this:
Step 4 of the Export Wizard asks that you specify which data you want included in the export file. Again, we want to include every piece of data. Therefore, click on the Go button again and then the blue double right arrow. Our step 4 looks like this:
Step 5 of the Export Wizard will list a summary of your Export Wizard choices. Ours looks like this:
Finally, we can click the Finish button.
As SQL Developer performs all of the tasks we just setup in the wizard, imagine several drum rolls and finally a cymbal crash sound as the DDL file opens within SQL Developer in its own tab. The imaginary drum rolls and cymbal sound isn’t necessary, but it enhances the wait time as SQL Developer performs our request.
Our DDL export file looks something like this:
As you can see, the DDL export file is really just a long list of SQL statements that can be used to recreate everything in your database.
If we look in our designated export storage folder, we can see our new DDL file:
If catastrophe strikes, you now have a backup of your database. If you need to rebuild your database, you can import the export file with just a few clicks in SQL Developer.
Wrap It Up
In this post we covered a few terms related to your Oracle data and backups. We also covered how to create a simple export of your database for use as a backup in case of catastrophe. You might want to consider creating these exports and backups at regular intervals.
Stay tuned for our next and final post in this series where we discuss using SQL Developer with other database products (MySQL). I, for one, am giddy with just the thought…