SQL Server vs Oracle Exporting Database Objects with SSMS and SQL Developer

By:   |   Updated: 2022-11-09   |   Comments   |   Related: More > Other Database Platforms


Problem

There are multiple ways to export an entire database or a partial database and in this article, we will look at how this can be done in Oracle and also in SQL Server.

Solution

In this tip, we will demonstrate how to export a database from both Oracle and SQL Server by showing the different screens of the respective wizards and discussing what options are offered.

Export Objects and Data from Oracle

Although it's common to perform a database export using "expdp", this tip will show how to do it using SQL Developer.

First, you need to launch SQL Developer:

SQL Developer

Connect to your Oracle database:

Database connection
Connection details

Next, go to the toolbar, select "Tools" and then "Database Export" to deploy the Export Wizard:

Database export

Step 1: Source/Destination

This is the first screen you will see and you can see there are many options outlined below.

Step 1

Export DLL

  • Add BYTE keyword: The column length is specified in bytes (not in characters).
  • Drops: Check to include the "drop" command.
  • Show Schema: Uncheck this when creating the objects under a different schema.
  • Add Force to Views: It will compile the views regardless of the dependent objects or permissions.
  • Grants: Uncheck to not include "grant" permissions.
  • Storage: Uncheck when creating the objects with different tablespace definitions.
  • Cascade drops: Check to include "drop" command for dependent objects (child).
  • Partitioning: Uncheck when removing partitioning.
  • Tablespace: Uncheck when creating a different tablespace definition.
  • Dependents: Check to include "create" for dependent objects (child).
  • Pretty Print: Uncheck to reduce the file size (not human-friendly).
  • Terminator: Uncheck to remove the carriage return at the end of each statement.
  • Version: COMPATIBLE to use the database compatibility level, LATEST to use the database version, or select version 10.1, 10.2, 11.1, 11.2, 12.1 or 12.2.

Export Data

  • Format: csv, delimited, xlsx, xls, excel.xml, fixed, html, insert (default), json, json-formatted, loader, pdf, t2, text, xml.
  • Line Terminator: platform default, environment default (default), Unix/Mac LF, Windows CR LF, CR, {EOL}.
  • Show Schema: includes owner to avoid errors with duplicate names.
  • Include Commit Every N Rows: check only if required to reduce undo/redo size with large tables.
  • Save As: single file, separate files, type files, separate directories, worksheet, clipboard.

Click Next.

Note: the navigation is the same for screens: back, next, finish and cancel.

Step 2: Types to Export

Choose the object types to export:

Step 2

Step 3: Specify Objects

If you want to export just a few objects, you can choose specific ones to export. Click "More" to choose from different schemas and select different object types, and note the button will change from "More" to "Less":

Step 3

Step 4: Specify Data

If you want to export just a few rows, you can use the specify data option to export using filters. You can filter objects, columns and use a "where" clause:

Step 4

Step 5: Export Summary

Review a summary of what will be exported:

Summary

If everything looks good, click Finish to export.

Export Objects and Data from SQL Server

This section will show how to export a database using SQL Server Management Studio.

First, launch SSMS:

SQL Server Management Studio

Connect to the SQL Server instance:

Connect to instance

Then, right-click on a database, select "Tasks" and "Generate Scripts":

Database generate scripts

Step 1: Introduction

Just click Next on the introduction screen.

Step 1

Note: the navigation is the same on all screens: previous, next, finish and cancel.

Step 2: Choose Objects

Choose to script entire database and all objects or select specific database objects. If you select specific database objects you will need to choose each object you want to export:

Step 2

Step 3: Set Scripting Options

Specify how scripts should be saved:

  • Save as notebook
  • Save as script file: single script file, one script file per object, file name, overwrite, Unicode or ANSI
  • Save to clipboard
  • Open in new query window
Step 3

Click Advanced in the upper right of the window for more options. This is an important step in the process.

Below are the Advanced Scripting Options that need clarification:

  • Convert UDDTs to Base Types: User-defined data types.
  • Include Descriptive Headers: Add comments.
  • Include Scripting Parameters Header: Add a header describing the selected options.
  • Script Bindings: For default and rule objects.
  • Script Defaults: Include default options explicitly.
  • Script for Server Version: 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019.
  • Script for the Database Engine Edition: Azure SQL Database Managed Instance, Azure SQL Edge, Enterprise, Express, Personal, Standard, Stretch Database.
  • Script for the Database Engine Type: Azure SQL Database, stand-alone.
  • Types of Data to Script: Schema only, data only, both.

There are several more options as shown below.

Step 3, Advanced scripting options

Step 4: Summary

On the next screen, the summary of selections is displayed:

Step 4

Step 5: Save Scripts

And on the last screen, you will need to wait for the export to complete, then you can open or save the report and finish the wizard:

Step 5

The wizard shows you what is being done.

Step 5 report

Conclusion

In both of the Oracle and SQL Server wizards, you can export schema only, data only, or both. Also, you can include drop and create statements, script grants, script-dependent objects, script for a lower database version, script permissions, etc.

In Oracle, you can also script things if you need to change the database configuration when importing it into another database: change schema, storage, tablespace, partitioning, etc. Also, in Oracle, you can script inserts for very large databases doing a commit every N rows.

In Oracle, you can filter data while creating the export, but in SQL Server you need to generate the schema script only and then use the "Import and Export wizard" if you want to filter the data.

Next Steps

Below are some links about scripting SQL Server databases:






get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria Pablo Echeverria worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implemented new processes, created better monitoring tools and grown my data scientist skills.

View all my tips


Article Last Updated: 2022-11-09

Comments For This Article

















get free sql tips
agree to terms