SQL - Rename Database :
Renaming a database in SQL involves a series of steps to ensure a smooth transition without losing any data or causing disruptions. Here's a detailed guide on how to rename a database in SQL:
Backup the Database: Before making any changes, it's crucial to back up the database to prevent data loss in case of any unforeseen issues.
Detach the Database: First, detach the database that you want to rename. This can be done using the following SQL command : -
USE master; GO ALTER DATABASE {CurrentDatabaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO EXEC sp_detach_db '{CurrentDatabaseName}';
Rename the Database Files: Next, rename the physical files of the database. This step involves renaming both the .mdf and .ldf files associated with the database.
Attach the Database with the New Name: After renaming the physical files, reattach the database with the new name using the following SQL command : -
USE master; GO CREATE DATABASE {NewDatabaseName} ON (FILENAME = '{NewDataFilePath}'), (FILENAME = '{NewLogFilePath}') FOR ATTACH;
Update System Catalogs: It's essential to update system catalogs to reflect the changes made to the database name. This can be done by querying the system catalogs to ensure that the new database name is correctly updated.
Verify the Changes: Finally, verify that the database has been successfully renamed by querying the system tables and running some test queries to ensure that the database functions as expected under the new name.
Rename Database in SQL using RENAME DATABASE...TO (obsoleted) : -
SQL provides a simple RENAME DATABASE...TO statement to rename an existing database. If you want to rename a database, make sure there is no active transaction in progress otherwise the complete operation might halt once you rename the database.
Syntax
Following is the syntax of the RENAME DATABASE...TO statement -
RENAME DATABASE OldDatabaseName TO NewDatabaseName;
Example
Before renaming a database, let us list down all the available databases -
SHOW DATABASES;
The output will be displayed as -
| Database |
|---|
| performance_schema |
| information_schema |
| mysql |
| sample_DB |
Now, issue the following command to rename the database sample_DB to test_DB:
RENAME DATABASE sample_DB TO test_DB;