SQL Rename Database

Filed Under: SQL

In the real world, there is very minimal chance when we need to rename database but still renaming database is a very valid scenario in real time.

Today we will try to rename databases in some of the most used Databases of the world.

Introduction

In this section, we will see how different databases provides the feature of renaming a database. So let’s start with the following set of databases.

  1. PostgreSQL
  2. MySQL
  3. SQLServer

We will try to understand database renaming in each of the above mentioned DBs one by one.

PostgreSQL

Let us try to rename a database in PostgreSQL.

Suppose the database that you want to rename is TestDB.

First, disconnect from the database that you want to rename by connecting to another database, we will connect to Postgres database.
By connecting to another database, you are automatically disconnected from the database to which you connected.

Before renaming any database it is always good to check if the database has an active connection.

We will also check in PostgreSQL if the database that we want to rename has any active connections.

SELECT
    *
FROM
    pg_stat_activity
WHERE
    datname = 'TestDB';

Output: –

PostgreSQL Check Connection

PostgreSQL Check Connection

As we see that the database has only one connection. If the database has many connections it is better to inform the respective user about the rename.

After that, rename the TestDB database to NewTestDB using the ALTER DATABASE RENAME TO statement as follows:

ALTER DATABASE "TestDB" RENAME TO "NewTestDB";
PostgreSQL rename database

PostgreSQL rename database

MySQL

In the earlier version of MySQL rename database was done through a simple SQL command. But, due to security issues, the feature is revoked from the latest versions.

We can create a dumped copy, then create a new DB and then re-import from the dumbed copy.

SQL Command for Dump copy

$ mysqldump -u username -p"password" -R testDb > testDb.sql

SQL Command for creating new DB

$ mysqladmin -u username -p"password" create testDB1

SQL Command for Import

$ mysql -u username -p"password" testDb1 < testDb.sql

The above is one such solution for renaming a DB in MySQL

Also for Unix, database names are case-sensitive so please make sure that appropriate case is used.

SQLServer

We will now use SQLServer to rename a database. Let us rename TestDB using SQL Server Management Studio.

Please find below the steps to be followed to rename the database.

  1. Connect to the Database in the Object Explorer.
  2. We will try to make sure that there is no more connection to the DB before renaming.
  3. Set the connection to single user mode.
  4. Right Click on the database and click on properties.
  5. In the Database Properties box, click the Options page.
  6. From the Restrict Access option, select Single.
  7. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.
  8. Now right click the DB and click on Rename.
  9. Enter the new database name and click on OK button.
SQLServer Properties

SQLServer Properties

 

SQLServer Restrict Access

SQLServer Restrict Access

SQLServer Properties Rename

SQLServer Properties Rename

 

SQLServer Rename

SQLServer Rename

 

Once the rename process is complete please revert the SINGLE USER mode.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages