# Renaming Databases

There is no `RENAME DATABASE` statement. To rename a database, use one of the following procedures.

## Using RENAME TABLE

{% hint style="danger" %}
Use this procedure only if your tables have neither triggers nor views or events.
{% endhint %}

To move your tables from a database named db1 to one named db2, run these statements:

```sql
CREATE DATABASE db2;
# Do this for every table in database db1
RENAME TABLE db1.t TO db2.t;
# When no table is left in database db1, optionally drop it
DROP DATABASE db1;
```

{% hint style="warning" %}
Privileges are neither dropped for `db1`, nor are they "copied" to `db2`. Use [SHOW PRIVILEGES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-privileges) to see which privileges are related to `db1`, then [apply those privileges](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) to `db2`.
{% endhint %}

## Renaming Databases Manually

To overcome the limitations of the previous procedure, you can do the following.

In the following steps, the source database is named `PROD` , and the destination database `TEST`.

* **Full Backup and Restore**: If restoring to a different server, use [mariadb-backup](https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup) to create a full backup, then restore it to the intended server. (This step is optional. It is not necessary if your renamed database is to reside on the same computer.)

{% stepper %}
{% step %}
**Dump Logical Objects.**

`RENAME TABLE` does not work for triggers, events, and routines. You need to dump these logical objects separately.

```bash
mariadb-dump PROD --no-data --routines --triggers --events \
> PROD_routines_triggers_events.sql
```

{% endstep %}

{% step %}
**Generate RENAME TABLE Commands.**

Run the following query to generate a script with the necessary `RENAME TABLE` statements. This is much faster than a full logical dump.

```sql
mysql -ss -e"SELECT CONCAT('RENAME TABLE PROD.', TABLE_NAME, ' TO TEST.', \
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PROD'" \
> PROD_rename_table.sql
```

{% endstep %}

{% step %}
**List all Existing Objects.**

```sql
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_SCHEMA = 'PROD';
SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS 
       WHERE EVENT_SCHEMA = 'PROD';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE ROUTINE_SCHEMA = 'PROD';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS 
 WHERE TRIGGER_SCHEMA = 'PROD';
```

{% endstep %}

{% step %}
**Create the new Database.**

```sql
CREATE DATABASE TEST;
```

{% endstep %}

{% step %}
**Run the rename\_table Script.**

```bash
mysql TEST < PROD_rename_table.sql
```

{% endstep %}

{% step %}
**Restore Logical Objects.**

After the rename script completes, restore the triggers, routines, and events into the new database.

```bash
mysql TEST < PROD_routines_triggers_events.sql
```

{% endstep %}

{% step %}
**Verify all Objects are Restored.**

Verify that all objects have been correctly moved to the new `TEST` database.

```sql
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_SCHEMA = 'TEST';
SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS 
       WHERE EVENT_SCHEMA = 'TEST';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE ROUTINE_SCHEMA = 'TEST';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS 
       WHERE TRIGGER_SCHEMA = 'TEST';
```

{% endstep %}

{% step %}
**Cleanup.**

Once you have confirmed everything looks good, you can drop the old `PROD` database.

{% hint style="warning" %}
Privileges are neither dropped for `PROD`, nor are they "copied" to `TEST`. Use [SHOW PRIVILEGES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-privileges) to see which privileges are related to `PROD`, then [apply those privileges](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) to `TEST`.
{% endhint %}
{% endstep %}
{% endstepper %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/data-definition/renaming-databases.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.