# Clone a Database

> Make a new database from an existing one, under a new name, on the same server, from the API Maker admin panel. MongoDB, PostgreSQL, MySQL, MariaDB, SQL Server and Oracle are each copied the fastest way they have, on the server itself when they can, with their tables, rows, keys, indexes, counters, views, routines and triggers. The dialog shows each step, then each table beside its source.

Source: https://docs.apimaker.dev/v4/docs/features/clone-database.html

Make a new database from a database you have, under a new name, on the same server. Use it to try a migration on real data, to give a tester their own copy, or to keep a database as it is before a big change.

> **In short**
>
> - Select a database in the **Databases** card, then **⋮ → Clone Database** (**Clone Schema** for Oracle).
> - A free name is filled in, and the fastest way of the database is chosen.
> - The clone runs on the server. The dialog shows each step, then each table beside its source.

| | |
|---|---|
| Where | **APIs & Logic → Instance API (DB API)**, the **Databases** card : select one database, then **⋮ → Clone Database**. |
| Databases | MongoDB, PostgreSQL, MySQL, MariaDB, SQL Server, Oracle. |
| What comes along | The tables (collections) with their rows, primary, unique and foreign keys, checks and indexes. The counters of auto increments and sequences. The views, routines, triggers and events. |
| Speed | The database copies itself on its own server when it can : the rows never travel to API Maker. |
| The source | It is only read. Nothing in it changes. |
| Safety | The new name must be free on the server. A clone which fails or is stopped removes the database it made. |
| Jobs | A clone is a job of [Import & Export](https://docs.apimaker.dev/v4/docs/features/import-export.html#jobs) : it runs in the background, and its **Jobs** tab lists it with its log. |

## Clone a database

1. Open **APIs & Logic → Instance API (DB API)** and the instance. Select **one** database in the **Databases** card.
2. **⋮ → Clone Database**. The dialog reads the database : its tables, rows and size.
3. **New database** : a free name is already there (`shop_copy`, then `shop_copy_2`…). Type another one if you like. A name the database does not accept is shown as you type it, and so is a name which is already on the server.
4. **How** : the ways of this kind of database. The fastest one is chosen first.
5. **What** : **Structure and rows**, or **Structure only** (the same tables, keys and indexes, empty). A way which copies whole files always takes the rows : choose another way for the structure only.
6. Set the [options](https://docs.apimaker.dev/v4/docs/features/clone-database.html#options), then **Clone**.

The dialog then follows the clone : a bar, each step with what it does and how long it took, and the tables and rows done. **Stop** stops it, and the new database is removed. If you close the dialog, the clone goes on : follow it, with its log, in the **Jobs** tab of Import & Export.

When the clone is done, the **Databases** card lists the new database, and the dialog [compares it with its source](https://docs.apimaker.dev/v4/docs/features/clone-database.html#the-check-at-the-end).

## The ways

**built in** means the database does the copy itself. **fastest** marks the way chosen first.

| Database | Way | What it does |
|---|---|---|
| PostgreSQL | **Copy of the files (TEMPLATE)** · built in · fastest | `CREATE DATABASE … TEMPLATE` : the server copies the files of the database, with everything in it. No other session may use the database during the copy : see [below](https://docs.apimaker.dev/v4/docs/features/clone-database.html#postgresql). |
| | **The database's own programs, piped** | `pg_dump` writes straight into `pg_restore`. The database may stay in use. Nothing is written on a disk. |
| SQL Server | **Backup and restore** · built in · fastest | `BACKUP DATABASE … COPY_ONLY`, then `RESTORE … WITH MOVE`. The files of the new database are named after it. Your own chain of backups is not disturbed. The backup file is removed at the end. |
| | **Copy of Azure SQL (AS COPY OF)** · built in | Azure SQL Database copies the database itself. Only Azure SQL Database has it. |
| | **SqlPackage (.bacpac)** | SqlPackage exports a `.bacpac` and imports it into the new database. Slower : for servers which take no backup to their disk, such as Azure SQL Managed Instance. |
| Oracle | **Data Pump (REMAP_SCHEMA)** · built in · fastest | Data Pump exports the schema into the directory of the server (`DATA_PUMP_DIR`) and imports it under the new name. |
| MySQL, MariaDB | **Copy on the server** · fastest | Each table is made as `SHOW CREATE TABLE` says, then filled by `INSERT … SELECT` on the server. A table whose key is one whole number goes 50,000 rows at a time. |
| | **The database's own programs, piped** | `mysqldump` (or `mariadb-dump`) writes straight into `mysql`. Nothing is written on a disk. |
| MongoDB | **Copy on the server** · fastest | Each collection is made with its options and filled by `$merge` on the server. Its indexes are made after its documents, which is faster. |
| | **The database's own programs, piped** | `mongodump` writes straight into `mongorestore`, which renames the collections on the way. |

The programs of the piped ways are the ones of [Import & Export](https://docs.apimaker.dev/v4/docs/features/import-export.html#the-tools-on-sandbox-settings) : installed from their publishers the first time a job needs them.

**One moment** : the files of PostgreSQL, the backup of SQL Server, the copy of Azure SQL, Data Pump and `pg_dump` copy every table as it was at one moment. The other ways copy one table after the other : a row written into the source during the clone may be in the new database or not. The dialog says which one you chose.

## What comes along

- **Keys** : primary, unique and foreign keys and checks keep their columns. A name the database gave a key itself (`SYS_C…` in Oracle) gets a new one from the database.
- **Counters** : the next value of each auto increment and sequence is the one of the source. A new row in the clone does not reuse an id.
- **Views, routines, triggers, events** : made after the rows, so a trigger does not fire for the rows of the copy. Events of MySQL and MariaDB are made **disabled** : the source runs them already. Turn them on in the clone when you want them.
- **Names inside code** : in MySQL, MariaDB, SQL Server and Oracle, a view, routine, trigger or event whose text names the source database (`shop.orders`, `shop.dbo.orders`, `SHOP.ORDERS`) is made again with the new name. So the clone reads and writes its own tables, never the ones of the source. Only a name which is the database changes : a table, schema or alias with the same name as the database keeps it (`shop.id`, where `shop` is a table). PostgreSQL and MongoDB need nothing : their objects do not name their database.
- **MongoDB** : capped collections, time series, clustered collections, validators and collations come along. Views are made last, a view after the views it reads.

## Options

| Option | What it does |
|---|---|
| **Copy the schemas of its tables** | The [table schemas](https://docs.apimaker.dev/v4/docs/schema/schema.html) your account keeps for the tables of the source are copied for the new database, each under a new name. They are new items of the project : they go to Git with your next commit. |
| **Close the other sessions of `<database>`** | PostgreSQL, with **Copy of the files** only. The sessions which use the database are closed, so the copy can start. Without it, the clone stops and names who uses the database. |
| **Gently** | The programs the clone runs on this server get the least share of its processors and disks : the APIs of the server go first. What the database does itself runs as the database runs it. |

## Names

- A free name is offered : the name of the source with `_copy`, then `_copy_2`, `_copy_3`… It is free both on the server and among the names your account gives.
- The new name must not be on the server already, and must not be the source itself.
- Names of the server itself are refused : `admin`, `config`, `local` (MongoDB), `postgres`, `template0`, `template1` (PostgreSQL), `mysql`, `sys`, `information_schema`, `performance_schema` (MySQL, MariaDB), `master`, `model`, `msdb`, `tempdb` (SQL Server), `SYS`, `SYSTEM` and the other users of Oracle itself.

| Database | A name |
|---|---|
| MongoDB | up to 63 bytes, without a space or any of `/` `\` `.` `"` `$` `*` `<` `>` `:` `\|` `?`. Two names which differ only by their case are one database. |
| PostgreSQL | up to 63 bytes, without `"`. The case counts. |
| MySQL, MariaDB | up to 64 bytes, without `/`, `\`, `.` or a back quote, and no space at the end. |
| SQL Server | up to 128 bytes, without any of `[` `]` `"` `'` `/` `\` `:` `*` `?` `<` `>` `\|` : the files of the database are named after it. |
| Oracle | a letter, then letters, digits, `_`, `$` or `#`, up to 128 bytes. It is kept in capitals : `shop_copy` becomes `SHOP_COPY`. |

## Masked database names

With [masked database names](https://docs.apimaker.dev/v4/docs/features/team-work.html#mask-database-names), your account calls a database by the name of the project, and the server holds it under another name. The clone follows the mapping :

- It copies the database of the server the source stands for. The dialog shows that name under **On this server**.
- The new name is a name of the project too. If your account masks it, the dialog says **Made as `shop_test_alice` on this server**, and that is the database the clone makes.
- With **Copy the schemas of its tables**, the copied schemas use the name of the project.

## Database by database

### PostgreSQL

**Copy of the files** needs the source to be used by no session while the server copies it. API Maker closes its own connections to the database first. When other programs use it, the clone stops and names them (user, program, address). Then either turn on **Close the other sessions**, or choose **The database's own programs, piped**, which copies a database in use.

### SQL Server

**Backup and restore** writes the backup into the default backup folder of the server, then removes it. It is refused on Azure SQL Database (choose **Copy of Azure SQL**) and on Azure SQL Managed Instance (choose **SqlPackage**). A backup holds the rows with the tables : for the structure only, choose SqlPackage. SqlPackage runs on x86-64 servers only.

### Oracle

In Oracle, a schema is a user. The clone makes a new user, so the dialog asks for its **password**, twice. The password of the source never goes along, and the one you type is never stored : not in the job, not in the log.

- The user of the instance needs the role `DATAPUMP_IMP_FULL_DATABASE` (or `DBA`) to import a schema under another name. With `DATAPUMP_EXP_FULL_DATABASE` too, the new user gets the rights and quotas of the source.
- The dump goes through the directory object `DATA_PUMP_DIR`. Its files are removed at the end.
- A trigger whose text named a table of the source (`ON SHOP.ORDERS`) is made again on the table of the new schema. Then the new schema is compiled, and what stays invalid is said in the log.

### MySQL and MariaDB

The new database takes the character set and collation of the source. The checks of foreign keys are off while the tables are made, so the order of the tables does not matter. Columns the server computes are left out of the copy : the server computes them again.

### MongoDB

`$merge` cannot write into a capped collection, a time series or `system.js`, so these take their documents through API Maker, in their order. **The database's own programs, piped** always copies the documents : for the structure only, choose **Copy on the server**.

## The check at the end

The last step compares the new database with its source, table by table : the columns, the indexes, and the rows.

| A table is | Means |
|---|---|
| **same** | the same columns and indexes, and the same number of rows |
| **estimated** | a large table : its rows were not counted, the catalog estimates them |
| **rows differ** | the clone has another number of rows (rows written into the source during the clone, with a way of no one moment) |
| **columns differ**, **indexes differ** | the structure is not the same : the warnings of the job say how |
| **missing** | the clone has no such table |

With **Structure only**, the rows are not compared. Each table that differs is a warning in the log of the job.

## When a clone fails

- The step which failed turns red, with the error. The full log is open below it.
- The new database is removed, if the clone made it. A database which could not be removed is named in a warning : remove it by hand.
- **Try again** opens the form with what you chose.

If the server stops during a clone, the job is marked failed when the server starts again, like every job of Import & Export. The database the clone was making is removed then.

## Related

- [Import & Export](https://docs.apimaker.dev/v4/docs/features/import-export.html) : copy a database into a file, and into another server.
- [Team work](https://docs.apimaker.dev/v4/docs/features/team-work.html#mask-database-names) : masked database names.
- [Table designer](https://docs.apimaker.dev/v4/docs/schema/table-designer.html) : change the tables of the clone.
