How to Rename a Database in SQL

1. Overview

We sometimes need to rename a database when restructuring a project, aligning with new naming conventions, or correcting the initial name. However, the method for renaming varies depending on the SQL platform.

In this tutorial, we’ll show how to rename a database in three major SQL database management systems: SQL Server, PostgreSQL, and MySQL.

The examples we’ll discuss are based on the Baeldung University database.

2. In SQL Server

In SQL Server, we can use the ALTER DATABASE statement to rename a database:

ALTER DATABASE [OldDatabaseName] MODIFY NAME = [NewDatabaseName];

We replace [OldDatabaseName] and [NewDatabaseName] with old and new database names.

Before renaming, we must ensure there are no active connections to the database. If there are active connections, we should set the database to the single-user mode:

ALTER DATABASE University SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This will immediately disconnect all users from the University database and allow the rename to proceed.

Now, we run the ALTER DATABASE statement:

ALTER DATABASE University MODIFY NAME = NewUniversity;

After renaming, we revert the database to the multi-user mode:

ALTER DATABASE NewUniversity SET MULTI_USER;

3. In PostgreSQL

Like SQL Server, PostgreSQL also supports ALTER DATABASE statements for renaming a database but there’s a slight difference in the syntax. However, while SQL Server uses MODIFY NAME, PostgreSQL uses RENAME TO for renaming.

Additionally, PostgreSQL doesn’t allow renaming a database if there are active connections. We can resolve this by first terminating active connections:

# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'University';
 pg_terminate_backend
----------------------
(0 rows)

Afterward, if we want to rename the database University to University1, we execute the following query:

# ALTER DATABASE University RENAME TO University1;
ALTER DATABASE

4. In MySQL

In MySQL, there’s no direct support for renaming a database through ALTER DATABASE or RENAME DATABASE.

Instead, we create a new database and move all the tables from the old database to the new one using the RENAME TABLE statement. Of course, we have to adjust the database permissions after that.

For instance, let’s say we want to rename the database University to New_Univeristy in MySQL.

First, let’s create the new database New_University:

CREATE DATABASE New_University;

Next, we move all tables to the New_University database using the RENAME TABLE statement:

RENAME TABLE University.Department TO New_University.Department, 
University.Student TO New_University.Student, 
University.Course TO New_University.Course;

After moving the tables, we can drop the old University database:

DROP DATABASE University;

4.1. Using mysqldump

Alternatively, we can use the mysqldump utility to export the data from the old database and import it into the new one:

mysqldump -u root -p University > university_backup.sql
Enter password: ****

It creates the backup in the university_backup.sql file.

Now, we import the backup to the new database using the mysql command:

mysql -u root -p New_University < university_backup.sql
Enter password: ****

Once done, we can drop the old University database.

This approach is helpful when dealing with large databases containing many tables.

5. Conclusion

In this article, we explored renaming a database in SQL Server, PostgreSQL, and MySQL.

SQL Server and PostgreSQL offer direct queries like ALTER DATABASE to change the database name. Meanwhile, MySQL requires creating a new database and manually moving tables using the RENAME TABLE statement or the mysqldump utility for large databases.