SQL - Rename Database

SQL: Renaming a Database

General Considerations

Renaming a database is a significant administrative action that can have wide-ranging effects. Before proceeding, consider the following:

• Application Downtime: Applications connected to the database will lose their connection and will need to be reconfigured with the new database name.
• Exclusive Access: Most database systems require that there are no active connections to the database you intend to rename. You may need to put the database in single-user mode or manually disconnect all users.
• Backups: Always perform a full backup of the database before attempting to rename it. This provides a safe recovery point in case anything goes wrong.

The command to rename a database is specific to the Relational Database Management System (RDBMS) you are using.

Renaming in PostgreSQL

PostgreSQL provides a straightforward command for renaming a database. You must be the database owner or a superuser to execute this command.

Syntax

ALTER DATABASE current_name RENAME TO new_name;

Example

-- Ensure no one is connected to 'testDB' before running
ALTER DATABASE testDB RENAME TO tutorialsDB;

Renaming in SQL Server

In SQL Server, you use the ALTER DATABASE command with the MODIFY NAME option. It’s best practice to first set the database to single-user mode to ensure no one else is connected.

Syntax

ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

Example

-- Set to single user mode to kick out other connections
ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Rename the database
ALTER DATABASE testDB MODIFY NAME = tutorialsDB;

-- (Optional) Return to multi-user mode
ALTER DATABASE tutorialsDB SET MULTI_USER;

Renaming in MySQL (The Manual Way)

MySQL does not have a direct, single command to rename a database safely. The old RENAME DATABASE command was removed due to being unsafe. The standard, recommended procedure is to dump the old database, create a new one with the desired name, and import the data.

This multi-step process is performed from your system’s command line.

Step 1: Dump the Old Database

Use the mysqldump utility to create a SQL file containing the structure and data of your old database. The -R flag ensures routines and triggers are included.

$ mysqldump -u [username] -p -R old_db_name > old_db_backup.sql

Step 2: Create the New Database

Connect to MySQL and create an empty database with the new name.

$ mysql -u [username] -p -e "CREATE DATABASE new_db_name;"

Step 3: Import the Data into the New Database

Import the SQL backup file into your newly created database.

$ mysql -u [username] -p new_db_name < old_db_backup.sql

Step 4: Verify and Clean Up

After thoroughly verifying that the new database works as expected, you can safely drop the old database.

$ mysql -u [username] -p -e "DROP DATABASE old_db_name;"

Many graphical database tools like MySQL Workbench or DBeaver can simplify this process by providing a user interface for these steps.

Deprecated Methods

A RENAME DATABASE command existed in very old versions of MySQL. It has been removed from all modern versions because it was not atomic and could lead to data loss. You should not rely on this command or expect it to be present.