Skip to content

Latest commit

 

History

History
124 lines (79 loc) · 6.17 KB

rename-a-database.md

File metadata and controls

124 lines (79 loc) · 6.17 KB
title description author ms.author ms.reviewer ms.date ms.service ms.topic helpviewer_keywords monikerRange
Rename a database
Learn how to rename a user-defined database in SQL Server, Azure SQL Database, or Azure SQL Managed Instance.
WilliamDAssafMSFT
wiassaf
randolphwest
07/25/2024
sql
how-to
databases [SQL Server], renaming
renaming databases
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

Rename a database

[!INCLUDE SQL Server Azure SQL Database]

This article describes how to rename a user-defined database in [!INCLUDE ssnoversion], [!INCLUDE ssazure-sqldb], or [!INCLUDE ssazuremi-md], by using [!INCLUDE ssManStudioFull] (SSMS) or [!INCLUDE tsql] (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

Permissions

Requires ALTER permission on the database.

Use SQL Server Management Studio (SSMS)

Use the following steps to rename a [!INCLUDE ssnoversion] 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 [!INCLUDE ssnoversion], and then expand that instance.

  3. Make sure that there are no open connections to the database. If you're using [!INCLUDE ssnoversion], 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 [!INCLUDE ssnoversion] 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 [!INCLUDE ssnoversion], 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 [!INCLUDE ssnoversion] 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