SQL RENAME DATABASE statement is used to rename a database. Sometime we need to rename database if in later stage stage we find that database name should be more relevant or if we want to create a temporary database.
Now lets check how this SQL RENAME DATABASE works in MySQL and sqlserver.
RENAME MySQL database
In the earlier version of MySQL database rename of database used to be done using simple RENAME DATABASE statement. But this method has been discontinued in the latest version of the database due to security reason.
In order to rename MySQL database, you need to export the data from the existing database and import the database after creating new database. You need to follow the below steps.
Step #1 : Create Dump of Database
$ mysqldump -u username -p"password" -R testDb > testDb.sql
Step #3: Create a new database
$ mysqladmin -u username -p"password" create sampleDB
Step #3: Import the database
$ mysql -u username -p"password" sampleDB < testDb.sql
Rename SQL Server database
The below command is useful for SQL server 2005, 2008, 2008R2, 2012 and 2014.
ALTER DATABASE old_database_name MODIFY NAME=new_database_name;
We can also rename SQL server database name using SQL server database studio.
You can follow the below steps.
1. In the Object Explorer, connect to the database.
2. We need to ensure that there are no connection to the database which we are going to rename.
3. We need to set the single connection mode.
4. Right click on the database and click on properties.
5. In the database properties window, click the options page.
6. In 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. Next right click on the DB and click on Rename.
9. Enter new database name and click ok button.
10. Once rename is done, revert the single user mode. So it can be accessible to all.