So to rename the database, you need to follow one of the methods as discussed below. First of all, we have to create a database which needs to be renamed.
Database Creation:
Select any database in your SQL Server Management System (SSMS). If you don’t have any you can create one by following this procedure. There are multiple methods to rename a database in SQL Server, which are discussed below in detail along with versions supported by any specific method. Later on, we will also discuss errors that arise while renaming the database, and procedure to fix them.
Method 1: Using SSMS rename option to rename the SQL Server Database
This is the simplest way to rename a database. You have to proceed like this to do so.
Method 2: Using SSMS to rename the SQL Server Database
Another simplest way to rename a database just like renaming any folder in the windows. You have to continue like this to do so. Click on the database to rename it just like renaming windows folders.
Method 3: Using T-SQL to rename the SQL Server Database
For SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017, this command works. Execute the following statement. The output will be “The database name ‘test_cases’ has been set.” As seen in the figure below. You can use the T-SQL command below to change the name of the database if you are using SQL Server 2000. With SQL 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017, this still works, but at some stage, Microsoft claims it will be phased out.
Method 4: Using detach and attach rename the SQL Server
Using SQL Server’s detach and attach feature can be used to remove the database first and assign the database a different name when you re-attach the database. The following T-SQL commands can be used to do this Detaching the database by executing the following code: The output will be like this Attaching the database. The output will be:
Using SSMS to detach and reattach databases
This can also be achieved using SSMS by following these steps. Furthermore, we will discuss errors that may arise while renaming the database. You will need exclusive access to the database to rename the database, which ensures that the database does not have any other database connections. But if this is not the case, it can result in errors as shown below. The error can even arise when we accidentally open another window with the same database name which has to be renamed.
Errors that arise while renaming the database via SSMS and by executing the query:
These below mentioned two errors have two different views because they arise while renaming the database using two different methods. Error 1 arises while renaming the database using SSMS and the second error arises while renaming the database using query. Both errors show the same message “The database could not be exclusively locked to perform the operation”, that means the error arises as the user is not given exclusive access to the database to rename a database. So to rename the database we need to set the database in “single-user mode“.
Error 1: Error arises while renaming the database via SSMS
“Unable to rename test. (ObjectExplorer) Additional information: Rename failed for Database test (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)”
Error 2: Error arises while renaming the database using query
“Msg 5030, Level 16, State 2, Line 2. The database could not be exclusively locked to perform the operation.” First of all, we will reproduce the above-mentioned error messages for understanding that in which scenario they arise and how to fix them.
Solution:
This will configure the database to roll back any pending transactions and set it in “single user mode” and then back into “multi-user mode”.
Conclusion:
These are multiple ways to rename the database. If one way is not working for you. You can go to another one. One thing that should be kept in mind that changing database names using these ways just rename the database. The “physical files” still have the same names. As we can see in the figure below we have changed the database name from “test” to “test_cases” but in the physical location, it remained the same. So the simplest approach is to use solution 4 if you want to change the file name as well. You must first change the name of the physical files before reattaching the files and then specify the renamed files when you do the reattach. Moreover, in addition to changing the names of the databases, you also need to check if there are any references in your application code to the database name. This can be either within SQL Server or outside of SQL Server.
How to Setup Database Mail in SQL Server using Gmail?How to: Bulk Rename Files using the Bulk Rename UtilityHow to Monitor your Database Performance using Database Performance Analyzer?How to Split a String by a Delimited Char in SQL Server?
