Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
This article describes how to rename a user-defined database in SQL Server, Azure SQL Database, or Azure SQL Managed Instance, by using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL). The name of the database can include any characters that follow the rules for identifiers.
Note
To rename a database in Azure Synapse Analytics or Parallel Data Warehouse, use the RENAME statement.
System databases can't be renamed.
The database name can't be changed while other users are accessing the database.
Use SSMS Activity Monitor to find other connections to the database, and close them. For more information, see Open Activity Monitor in SQL Server Management Studio (SSMS).
In SQL Server, you can set a database in single user mode to close any open connections. For more information, see set the database to single-user mode.
In Azure SQL Database, you must make sure no other users have an open connection to the database to be renamed.
Renaming a database doesn't change the physical name of the database files on disk, or the logical names of the files. For more information, see Database Files and Filegroups.
It isn't possible to rename an Azure SQL database configured in an active geo-replication relationship.
Requires ALTER
permission on the database.
Use the following steps to rename a SQL Server or Azure SQL database using SSMS.
In SSMS, select Object Explorer. To open Object Explorer, press F8. Or on the top menu, select View > Object Explorer:
In Object Explorer, connect to an instance of SQL Server, and then expand that instance.
Make sure that there are no open connections to the database. If you're using SQL Server, you can set the database to single-user mode to close any open connections and prevent other users from connecting while you're changing the database name.
In Object Explorer, expand Databases, right-click the database to rename, and then select Rename.
Enter the new database name, and then select OK
If the database was your default database, see Reset your default database after rename.
Refresh the database list in Object Explorer.
Use the following steps to rename a SQL Server database using T-SQL in SSMS, including the steps to place the database in single-user mode. After the rename, this example places the database back in multi-user mode.
Connect to the master
database for your instance.
Open a query window.
Copy and paste the following example into the query window and select Execute. This example changes the name of the MyTestDatabase
database to MyTestDatabaseCopy
.
Warning
To quickly obtain exclusive access, the code sample uses the termination option WITH ROLLBACK IMMEDIATE
. This cases all incomplete transactions to be rolled back and any other connections to the MyTestDatabase
database to be immediately disconnected.
USE master;
GO
ALTER DATABASE MyTestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy;
GO
ALTER DATABASE MyTestDatabaseCopy SET MULTI_USER;
GO
Optionally, if the database was your default database, see Reset your default database after rename.
Use the following steps to rename an Azure SQL database using T-SQL in SQL Server Management Studio.
Connect to the master
database for your instance.
Open a query window.
Make sure that no one is using the database.
Copy and paste the following example into the query window and select Execute. This example changes the name of the MyTestDatabase
database to MyTestDatabaseCopy
.
ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy;
After renaming a database in SQL Server, back up the master
database. In Azure SQL Database, this process isn't needed, as backups occur automatically.
If the database you're renaming was set as the default database of a SQL Server login, they might encounter Error 4064, Can't open user default database
. Use the following command to change the default to the renamed database:
USE [master]
GO
ALTER LOGIN [login] WITH DEFAULT_DATABASE=[new-database-name];
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today