SQL Database is one of the popular databases that is used by users worldwide. However, few problems may arise in SQL database due to corruption, sometimes renaming database could solve these problems to a major extent otherwise we need to repair SQL Database if renaming SQL database doesn’t solve the problem. Considering a situation like you backup database with a temporary name and the original database has a different name, so before restoring the database rename it to the original database.
There are several methods to rename the database using Transact-SQL and SQL Server Management Studio, but it requires certain conditions. Before renaming the database it should be in single mode. So, the whole process is executed in two major steps
- Set Database to Single User Mode
- Rename the database
Here we will learn how to set database to single user mode and rename it using SQL Server Management Studio
Points to Remember
- Single User Mode is to be set as if multiple users will be connected to the database then the actions performed on the database will interfere with the renaming process. Moreover, if a user executes the query on the old database name then it will not be processed.
- User should have the permission for ALTER command to change the database name
- To set and access the Single user mode in SQL database make sure AUTO_UPDATE_STATISTICS_ASYNC is set to OFF.
- Make sure there is no external connection to SQL Database
Set SQL Database to Single User Mode
- Start the SQL Server Management Studio
- Open the Object Explorer, connect and expand instance of SQL Server Database Engine
- Select the database, then right click on it and choose ‘Properties’
- A new window will open, click on ‘Options’
- From the Restrict Access Option, select ‘Single’
- If other users are connected with database then Open Connections, change the property and close all other connections
- Confirm Yes, the SQL server database will be set to single-user mode only
Now, once it is set to single user mode the database name can be changed easily by following method
Rename SQL Server Database
- Start SQL Server Management Studio and open Object explorer
- Establish a connection with SQL instance
- Click on the database of which you want to change the name
- Right click on the database and click Rename
- Type a new name to the database and then click OK
- Exit SQL Server Management Studio
By following the above steps the name of a SQL database can be changed and it can be used to perform other actions like database transfer or migration of tables, indexes and other components.