From the object explorer's right-click menu you can export a table's data to a file and load a file's data into a table. You can also back up tables, schemas and databases with the dump tools your DBMS provides (pg_dump, mysqldump and so on) and restore from those files. All four features work only in the desktop app, because the desktop app's local server reads and writes files on this PC (Web and desktop).
What's on the screen
| No. | Menu | What it does | Shown on |
|---|---|---|---|
| 1 | Saves every row of the table to a CSV, JSON or Excel file | Tables that exist in the real database | |
| 2 | Creates a backup file with the DBMS's dump tool | Depends on the DBMS (see 'Native Backup' below) | |
| 3 | Inserts the rows of a CSV, JSON or Excel file into the table | Tables that exist in the real database | |
| Restores a backup file into the database | Depends on the DBMS (see 'Native Reload' below) |
- Tables created in the project but not yet applied to the database, and tables marked for deletion, don't show or .
- Select several tables of the same connection and database together (click while holding Ctrl or Cmd) and right-click to export or import them in one go.
Export data
- Right-click a table and choose . The Export Data dialog opens. For several tables it is the Export Data · 3 tables dialog.
- In the Format step, choose CSV, JSON or Excel.
- In the Options step, set the options for that format (Field delimiter, CSV Encoding and Header for CSV; long-cell handling and file splitting for Excel, and so on).
- In the Preview step, check what the file will look like.
- In the Output step, set the Output folder and the file name, then click the Download button.
- The dialog closes and the export continues in the background. The local server reads the table's rows from the database and writes them straight to the file, so rows never loaded on screen are included too.
- When you export several tables, all files are saved in one folder. If names collide, the schema name or a number is added. The number of tables exported at the same time is set in Concurrent export jobs.
- A file whose values had to be altered because of the format's limits gets
_lossyin its name. That file can't be imported back as the original data. - The options are the same as in the dialog that exports SQL results. Each option is described in Exporting results.
Import data
- Right-click a table and choose . The Import Data · orders dialog opens.
- In the Format step, choose the file format (CSV, JSON or Excel).
- In the Options step, drop or choose the file. For CSV choose the encoding, delimiter and Use first row as column names; for Excel choose the sheet. Data commit policy is also set here (table below).
- In the Column Mapping step, decide the value for each column of the table. If the first row holds column names, file columns with the same name (ignoring case) are connected from the start; otherwise file columns are connected in order. Check the values in the File Preview (first 20 rows) table below.
- Click the Start Import button. The dialog closes and the import runs in the background. When it finishes, the Data Import Result window shows the Processed rows, Committed rows and Failed/skipped rows items.
Value source offers five choices for each column.
| Value source | Value inserted |
|---|---|
| File column | The value of the chosen file column |
| Fixed value | The same value for every row |
| Database default | The column's database default |
| NULL | NULL |
| Do not import | Leaves the column out of the INSERT. Required columns can't be left out |
Data commit policy decides how much is rolled back when an error occurs or you cancel.
| Policy | When it commits | On failure or cancel |
|---|---|---|
| Commit after full success | Once, after the whole file succeeds | Everything is rolled back |
| Commit every N rows | Every given number of rows | Only the rows after the last commit are rolled back |
If a row fails during the import, the import pauses and the Data Import Error window shows that row's number in the file, the target column and the original value. Choose the Skip, Skip all, Retry or stop button. The stop button appears as Stop and roll back all for the Commit after full success policy as Stop and roll back uncommitted rows for Commit every N rows and otherwise as Stop (keep committed rows). Stopping rolls back according to the policy above.
Import several tables at once
Drop the files together onto the Upload Multiple Files area in the Options step. A file whose name contains a table name is connected to that table automatically; Excel files are also matched by worksheet name. Import order starts out with parent tables first based on the FKs, and you can drag to change it. Tables are imported one at a time in this order, and if one fails or is canceled, the tables after it aren't started.
Native Backup
Native Backup runs the DBMS's dump tool on this PC to create a backup file. Dump tools aren't bundled with NeoSQL, so install them on this PC first. H2 and SQL Server back up directly in the database without a dump tool (table below).
- Right-click the node to back up and choose . The Native Backup dialog opens.
- Choose the dump tool's executable in Local client. When you choose it, it is run once to show its version. The path you choose is remembered on this PC. The Local client field is not there for H2 and SQL Server.
- Check Output folder and File name. The file name includes the date and time.
- Click the Start Backup button. The dialog closes and the backup runs in the background.
| DBMS | Dump tool | File | Nodes you can back up |
|---|---|---|---|
| PostgreSQL | pg_dump | .dump | Table, schema, database |
| MySQL | mysqldump | .sql | Table, schema (= database) |
| MariaDB | mariadb-dump | .sql | Table, schema (= database) |
| Oracle | expdp (Data Pump) | .dmp | Table, schema, connection (whole database) |
| SQLite | sqlite3 | .sql | Table, connection (whole file) |
| H2 | Not needed | .sql | Table, connection |
| SQL Server | Not needed | .bak | Database (except system databases) |
- You can back up several databases of the same connection, or several tables of the same database, together. The number run at the same time is set in Concurrent jobs.
- For PostgreSQL, a warning appears when the dump tool's major version differs from the server's. The backup still runs, but the file may not restore, so use a tool of the same major version.
- Oracle creates the file in a Data Pump directory on the database server and then downloads it to the folder you chose. Enter the directory name in Oracle Data Pump directory; you need READ and WRITE on that directory. The file on the server is not deleted.
- SQL Server creates a copy-only full backup in a folder on the SQL Server host. The folder is a path on the database server, not on this PC, and the SQL Server service account must be able to write to it.
Native Reload
The menu restores a file made by Native Backup (or a dump made with the same tool) into the database.
- Right-click the node to restore into and choose . The PostgreSQL Native Reload dialog opens.
- Read the warning at the top of the dialog to see what restoring changes on this DBMS and what can't be rolled back.
- Choose the backup file in Backup file and the restore tool in Local client. If you chose a dump tool in Native Backup, the restore tool in the same folder is filled in.
- Click the Start Reload button. When it finishes, Native Reload completed. is shown and that node in the explorer is read again.
| DBMS | Restore tool | File | Node to restore into | Existing objects and rollback |
|---|---|---|---|---|
| PostgreSQL | pg_restore | .dump, .backup | Database | Existing objects aren't dropped. If names collide, the whole restore is rolled back |
| MySQL, MariaDB | mysql, mariadb | .sql | Schema (= database) | Runs the file as is, including its DROP, CREATE DATABASE and USE statements. What has already run isn't rolled back on an error |
| Oracle | impdp (Data Pump) | .dmp | Connection | Uploads the file to a Data Pump directory and restores it. Existing tables are skipped, and the whole job isn't rolled back |
| SQLite, H2 | sqlite3, not needed | .sql | Connection | Runs the SQL in the file. Changes already made may not be rolled back on an error |
| SQL Server | Not needed | .bak | Database (except system databases) | Replaces the database with the backup. Existing connections are closed, and it can't be rolled back once overwritten |
SQL Server backup files aren't uploaded from this PC. Enter a server path that the SQL Server service account can read in SQL Server backup file.
Progress and canceling
Export, import, backup and restore all continue as background tasks after the dialog closes. Click the task indicator in the status bar at the bottom to open the Background Processes list, where each task shows its progress and a Cancel button. For an import, the View result details button in this list reopens the result dialog even after it has finished. The status bar is described in App menu, top bar and status bar.
In the web app
The web app shows these four menus too. Clicking one only shows This feature is only available in the desktop app. and does nothing else. In the web app, use Exporting results to export SQL results.
Things to keep in mind
- A connection profile's protection mode also applies to the and menus. On a connection whose protection mode is Read-only (block non-SELECT), these two features do not start, and with Confirm (DDL + risky DML) the menu asks before you pick the file. Check the target connection again before using them on a production database (Safeguards for production databases).
- The menu can overwrite existing data or stop with only part applied, and on some DBMSs it can't be rolled back (table above). Back up the target database separately before restoring.
- In an account project, if your role's DDL is restricted, is dimmed. The same applies to a disabled connection.
- In an account project, if sensitive-column masking applies to your role, isn't shown, because the dump tools write the original data without masking.
- The dump and restore tool paths are remembered only on this PC. Choose them again on another PC.
Related screens
- Object explorer: the right-click menu that holds these items.
- Exporting results: the dialog and options for exporting SQL results to a file.
- Web and desktop: the list of desktop-only features.
- Safeguards for production databases: the safeguards that apply to production databases and the paths they don't cover.