Bewerken

Delen via

Rename a database

  • Artikel

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.

Limitations

  • System databases can't be renamed.

  • The database name can't be changed while other users are accessing the database.

  • 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.

Permissions

Requires ALTER permission on the database.

Use SQL Server Management Studio (SSMS)

Use the following steps to rename a SQL Server or Azure SQL database using SSMS.

  1. In SSMS, select Object Explorer. To open Object Explorer, press F8. Or on the top menu, select View > Object Explorer:

  2. In Object Explorer, connect to an instance of SQL Server, and then expand that instance.

  3. 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.

  4. In Object Explorer, expand Databases, right-click the database to rename, and then select Rename.

  5. Enter the new database name, and then select OK

  6. If the database was your default database, see Reset your default database after rename.

  7. Refresh the database list in Object Explorer.

Use Transact-SQL

Rename a SQL Server database by placing it in single-user mode

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.

  1. Connect to the master database for your instance.

  2. Open a query window.

  3. 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

  4. Optionally, if the database was your default database, see Reset your default database after rename.

Rename an Azure SQL Database database

Use the following steps to rename an Azure SQL database using T-SQL in SQL Server Management Studio.

  1. Connect to the master database for your instance.

  2. Open a query window.

  3. Make sure that no one is using the database.

  4. 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;

Backup after renaming a database

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.

Reset your default database after rename

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

Related content