SQL - Rename Database

---

---

The SQL RENAME DATABASE Statement

The RENAME DATABASE statement in SQL is used to change the name of an existing database. This is useful when you want to rename a project, fix a spelling mistake, or update your naming conventions.

However, it is important to know that most database systems, including MySQL, do not support this command directly. Instead of renaming, you usually have to create a new database with the desired name and manually copy the data from the old one.

Is RENAME DATABASE Supported in SQL?

No, the RENAME DATABASE statement is not supported in most modern SQL database systems, including MySQL. However, different systems handle database renaming in their own ways:

• MySQL: Does not support direct renaming anymore.
• SQL Server: Allows renaming using ALTER DATABASE ... MODIFY NAME command.
• PostgreSQL: Supports renaming with ALTER DATABASE ... RENAME TO command.

In earlier versions of MySQL, the RENAME DATABASE statement was allowed. But now it has been removed because it could cause data loss or corruption.

You might still find this old syntax in outdated tutorials or documentation:

RENAME DATABASE old_db_name TO new_db_name;

Using this command in MySQL will now result in an error.

Renaming a Database in MySQL

Since direct renaming is not supported in MySQL, you can safely rename a database by following these simple steps:

• Create a new database with the desired name.
• Export all data from your current (old) database. You can use a tool like mysqldump to generate a backup file containing all your tables and data.
• Import the data into the new database. This will copy everything from the old database into the new one.
• Delete the old database, but only after you have confirmed that everything works correctly in the new one. Make sure all data has been successfully moved and tested.

Example Using MySQL

Let us say you want to rename a database from oldDB to newDB. Since you can't rename it directly, here is how to do it step by step:

Step 1: Export the old database

mysqldump -u root -p oldDB > oldDB_backup.sql

This command creates a backup of the oldDB database. Here is what each part means:

• mysqldump: The tool used to export databases.
• -u root: Logs in as the MySQL user root.
• -p: Prompts you to enter the MySQL root password.
• oldDB: The name of the database you are exporting.
• > oldDB_backup.sql: Redirects the output to a file called oldDB_backup.sql, which contains the exported data and structure.

Step 2: Create the new database

CREATE DATABASE newDB;

This SQL statement creates a new, empty database named newDB. You will import your old data into this new database in the next step.

Step 3: Import data into the new database

mysql -u root -p newDB < oldDB_backup.sql

This command imports the data you backed up earlier into newDB. Here is what it does:

• mysql: The MySQL command-line tool is used to run SQL commands.
• -u root: Logs in as the root user.
• -p: Prompts for your password.
• newDB: The database where the backup will be restored.
• < oldDB_backup.sql: Tells MySQL to read and execute the SQL statements from the backup file.

Step 4: Drop the old database (only if everything works)

DROP DATABASE oldDB;

This command deletes the original oldDB database from your MySQL server. Only run this after you have confirmed that newDB has all your data and is working correctly.

By following these steps, you can rename your database without losing any data.

Renaming a Database in SQL Server

The ALTER DATABASE ... MODIFY NAME syntax is used specifically for renaming databases in Microsoft SQL Server. It is not supported in MySQL or PostgreSQL.

SQL Server Syntax

Following is the correct syntax to rename a database in SQL Server:

ALTER DATABASE current_database_name
MODIFY NAME = new_database_name;

Example

In this example, we reame the database testDB to tutorialsDB in SQL Server:

ALTER DATABASE testDB
MODIFY NAME = tutorialsDB;

We get the output as shown below:

The database name 'tutorialsDB' has been set.

Important Points to Remember:

Before renaming a database in SQL Server, consider the following precautions to avoid issues during execution:

• You can't rename a database while you are actively using it. So, before running the rename command, switch your connection to another database (like master) in SQL Server.
• If other users are currently connected to the database, SQL Server won't allow you to rename it. You need to make sure everyone is logged out or disconnected before you can perform the operation.
• Renaming a database using ALTER DATABASE ... MODIFY NAME updates only the name that SQL Server uses internally.
• It does not change the names of the actual data or log files on the disk (like .mdf or .ldf files).
• If you also want to rename those files, it's a separate process that involves detaching the database, renaming the files manually, and reattaching it.

Renaming a Database in PostgreSQL

The ALTER DATABASE ... RENAME TO command is used to rename a database in PostgreSQL. This syntax is specific to PostgreSQL and does not work in MySQL or SQL Server.

PostgreSQL Syntax

Following is the correct syntax to rename a database in PostgreSQL:

ALTER DATABASE old_database_name RENAME TO new_database_name;

Example

In this example, we rename the database testDB to tutorialsDB in PostgreSQL:

ALTER DATABASE testDB RENAME TO tutorialsDB;

Important Notes for Renaming a Database in PostgreSQL

Before using the ALTER DATABASE ... RENAME TO command, make sure to consider the following:

• You cannot be connected to the database you're trying to rename. Switch to a different database first (e.g., postgres).
• Ensure there are no active connections to the database you want to rename. You may need to disconnect users or force disconnection.