Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Clone a Database

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 : 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, 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.

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.
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 : 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 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, 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.