Rename a Database

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This topic describes how to rename a user-defined database in SQL Server 2017 or Azure SQL Database by using SQL Server Management Studio or Transact-SQL. The name of the database can include any characters that follow the rules for identifiers.

In This Topic

Note

To rename a database in Azure SQL Data Warehouse or Parallel Data Warehouse, use the RENAME (Transact-SQL) statement.

Before You Begin

Limitations and Restrictions

  • System databases cannot be renamed.
  • The database name cannot be changed while other users are accessing the database.
    • 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.

Security

Permissions

Requires ALTER permission on the database.

Rename a database using SQL Server Management Studio

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

  1. In Object Explorer, connect to your SQL instance.

  2. Make sure that there are no open connections to the database. If you are 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 are changing the database name.

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

  4. Enter the new database name, and then click OK.

Rename a database using Transact-SQL

To 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 SQL Server Management Studio including the steps to place the database in single-user mode and, after the rename, place 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 click Execute. This example changes the name of the MyTestDatabase database to MyTestDatabaseCopy.

    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
    

To 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 click 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 is not needed as backups occur automatically.

See Also