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.
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.
We will try to understand database renaming in each of the above mentioned DBs one by one.
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';
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";
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.
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.
- Connect to the Database in the Object Explorer.
- We will try to make sure that there is no more connection to the DB before renaming.
- Set the connection to single user mode.
- Right Click on the database and click on properties.
- In the Database Properties box, click the Options page.
- From the Restrict Access option, select Single.
- 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.
- Now right click the DB and click on Rename.
- Enter the new database name and click on OK button.
Once the rename process is complete please revert the SINGLE USER mode.