SQL Server RENAME Database
A database is a logically ordered collection of data, including tables, rows, columns, and indexes. It assists the user in finding important information on a regular basis, and it makes it simple for the user to access and manage records in the database. In SQL Server, all files are usually stored in the form of tables in each database.
We create a database for the purpose of the project requirements or maybe created while we are learning. Sometimes we need to change the database names because the previous name was based on a project that is no longer relevant to the database or because we gave a temporary name and now want to change it. This article will provide us with how to do so, what errors we might encounter, and how to resolve them regardless of why we want to rename the database.
We can rename a database in MS SQL Server in multiple ways. To rename a database, we can either use SQL Server management studio or perform a SQL query. Before renaming a database, it should be noted that it may disrupt connections for services or stored procedures configured with the current database name.
Limitations and Restrictions
SQL Server has some restrictions while renaming a database. Some of them are given below:
- We cannot change the system databases names.
- We cannot rename the database while other users are accessing the database.
- SQL Server allows us to set a database in single-user mode to close any open connections.
NOTE: We must have ALTER permission before renaming the database.
Rename a database with SQL Query
The ALTER command in SQL Server allows us to rename user-created databases. This command works with multiple versions of MS SQL Server, although Microsoft claims that it will be passed away at some point.
We can use the following syntax to rename databases:
In this syntax, the currentdb_name indicates the database name that we are going to rename. The newdb_name indicates the new name of the database.
Example
Consider we have a database named ‘example’ that is not relevant. Now, we want to change its name from ‘example’ to ‘exampledb.’ Here is the statement to rename it:
Executing the statement will show the following output that means ‘example’ database renamed to exampledb:
Once the database renaming is successful, we can verify the changed name as below:
Here is the output:
Rename a database with stored procedure
We can also use the standard stored procedure to change the user-created database name in SQL Server. The below syntax is used to rename the database:
Example
Consider we have a database named ‘TestDB’ that is not relevant. Now, we want to change its name from ‘TestDB’ to ‘mytestdb.’ Here is the statement to rename it:
Executing the statement will show the following output that means ‘TestDB’ database renamed to mytestdb:
Once the database renaming is successful, we can verify the changed name as below:
Rename database with SQL Server Management Studio (SSMS)
SSMS is a GUI tool that allows DBAs and database developers to configure, manage, and administer all components within SQL Server. Its main functionality is to create databases and tables, execute SQL queries for inserting, updating, and deleting data, creating and managing stored procedures, triggers, views, and cursors.
SQL Server allows us to rename the database in two ways:
- Rename a table using Object Explorer
- Double click on the table name
Let us discuss each of them in detail.
Rename a table using Object Explorer
Connect to the database server using SQL Server Management Studio (SSMS). You should ensure that there is no more connection to the database before renaming. Now, use the following steps to rename a database using Object Explorer:
Step 1: Go to the Databases menu and expand it to see the available databases in the current instance.
Step 2: Select the desired database you want to rename, right-click on it and choose the Rename option from the drop-down menu.
Step 3: Enter the new database name and then click OK. Now, refresh the object explorer to see the new database name. The below image shows that the ‘mytestdb’ database name is changed with ‘testdb’ database name:
Double click on the database name
SQL Server also allows DBA to change the database name by double-clicking on the desired name. We can use the following steps to rename the database using a double click:
Step 1: Go to the Object Explorer->Databases and expand it.
Step 2: Select the database you want to change and double-click on it. We can see that the database name is changed as an editable form. See the below image:
Step 3: Type a new name for your chosen database, press Enter, and refresh the object explorer to finish the renaming steps.
What happens if you try to rename a database that doesn’t exist?
When we try to rename a non-existing database, SQL Server throws an error message saying, “Can’t rename database ‘database_name’; database does not exist.”
Conclusion
This article gives the complete overview of changing the database name with the help of SQL command, stored procedure, and SQL Server Management Studio. It is always suggested that use T-SQL queries to make any changes in the database.