Rajendra Gupta
default database

Rename on-premises SQL Server database and Azure SQL database

October 18, 2022 by

This article explores how to rename Azure SQL Database and on-premises SQL instance database.

Introduction

Sometimes you need to rename your database because the name somehow didn’t work; the reason may be simple – maybe the name of your database was tied to another project that is no longer relevant. Database names should always reflect the content they hold and may need to change over time as your project ages. However, there are no specific requirements to tag a database with a valid name. However, it is best to use a naming convention for your databases to be referenced quickly.

For example, let’s say you have a database [test] on a production instance. At first glance, it appears to be a testing purpose database. Anyone with production instance access can play with this database, assuming this is not a valid production database.

Likewise, if you have similar database names in a SQL instance, for example, Mydatabase1, MyDatabase2. It would be challenging to find out applications pointing to these databases. You might need to go back into the inventory and look for the relevant details. Therefore, it is suggested to put valid names to guide you for the application using the database.

This article outlines steps to rename a user-defined database in SQL Server or Azure SQL Database using SQL Server Management Studio or Transact-SQL.

Rename a database for on-prem and Azure SQL Database

Before explaining the process to rename a SQL Database, let me highlight a few essential points below.

  • System databases: It is not allowed to rename SQL Server system databases – Master, MSDB, Model, and TempDB
  • If we rename a database, its physical or logical file name does not change
  • The database should not have any connection while we rename it. If there are any active connections, SQL Server raises an error
    • We can put the database into the single-user mode for the on-prem SQL database that closes any open connections
    • We cannot put the database into a single-user mode for the Azure SQL database. Therefore, you must ensure no open connection while renaming an Azure database
  • You cannot have two databases with similar names in an SQL Server instance
  • The user should have ALTER permission for renaming a SQL Database on the database

Let’s explore different ways to rename a database in SQL Server.

Use SQL Server Management Studio (SSMS)

Suppose I have a database [Import] in the on-prem SQL Server instance. I require the new database name as [MyTestDB].

Sample database

To rename a database, select the database, right-click on it, and choose Rename.

Renaming an existing database

It allows you to edit the database name. Specify a new name and press Enter. You get the following error message if there is an active connection for the SQL database. The error says the rename process could not lock the database exclusively to perform this database.

Exclusive database access

As stated earlier, we need to put the database into single-user mode to get exclusive access. The following query uses the option – ROLLBACK IMMEDIATE. It kills the existing transactions and initiates the rollback for the incomplete transactions.

Single user mode

Refresh the object explorer in SSMS, and you can see the alter database command modifies the database state into single-user mode.

view single user mode

Run the following script in the same session where we put the database into single-user mode.

Refresh SSMS object explorer again, and it reflects the new database name. However, the database is still in the single-user mode.

Database state

The following script changes the database from single-user mode to multi-user mode. Specify the new database name [MyTestDB] in the alter database script as shown below.

The database is now renamed successfully and accessible for user connections.

new database

The sp_helpfile system stored procedure returns the logical, physical name for the connected database. As shown below, it points to the Import database that was the name before renaming.

Output of the sp_helpfile

Things to do after remaining a SQL Server DB

  • Master database backup: You must take a full backup of the master database to have the latest system catalog in case of any issues. We cannot rely on the old master database backup because it has an entry for the old database name.
  • Reset default database for logins: We can have a default database for the logins. For example, the below screenshot shows the default database as Master for the TestLogin.

default database

If you have set a default database that you renamed to a different name, the login gets an error 4064: Cannot Open users default database while trying to connect to SQL Server database. To avoid the error and successful database connection, run the following script. It remaps the default database of the login to the new database name.

The renaming database process looks straightforward. However, it is advisable to plan it correctly with the following. Before you plan to rename a database, make sure to do the following.

  • List out application and user connections that connect to the database regularly. You require replacing the new database name in the connection strings for a successful database connection.
  • You need to note SQL agent jobs (on-prem) or Azure functions, scripts that reference the Azure database to execute queries.
  • You require remapping the monitoring platforms to the correct database (new name) if it connects to the specific database.

Rename an Azure SQL Database

You might have a database deployed in Azure infrastructure. Suppose you get a requirement to rename the Azure database as well.

For this example, I have an Azure database named [azuredemosql]. Let’s say we need to rename it as [myazuredb].

Azure sql database rename

We should have any connection to the Azure database while renaming it. We cannot change the database state into a single-user mode for Azure. If we try to do so, it gives the following error.

Msg 42008, Level 16, State 5, Line 3
ODBC error: State: 42000: Error: 1468 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The operation cannot be performed on database “azuredemosql” because it is involved in a DB mirroring session, or an availability group. Some operations not allowed on a database that participating in a database mirroring session or in an availability group.’.
Msg 5069, Level 16, State 3, Line 3
ALTER DATABASE statement failed.

ALTER DATABASE statement

In my experience, the alter database script for renaming a database works even if you have an existing database connection. However, the existing connected users start getting the error as below. Therefore, you should plan the database renaming well to avoid broken user connections.

view connection state

Execute the following script for renaming an Azure SQL Database.

Modify database name

Note: We need to connect to the master logical database in Azure SQL Server to rename an Azure SQL Database. Azure SQL does not allow using the database name with the USE DATABASE statement.

The above script renames the database to the desired name. As shown below, the new database name is [MyTestDB].

verify database name

Azure automatically takes database backup; therefore, you do not need to take explicit database backup for the Azure SQL database after renaming it. For new database connections, make sure you specify the correct database name. For example, the following screenshot specifies the new database name in the box – Connect to database:.

Connect to database wizard

Conclusion

In this article, we learned about renaming an Azure SQL Database. You can rename the database from SQL Server Management Studio or T-SQL script. It is advisable to do it when there is no user connection so that the application does not face any issue until you point them to the correct database.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Exporting SSRS reports to multiple worksheets in Excel
  2. SQL Server 2017: Columnstore Indexes and Trivial Plan
  3. Getting started with Azure SQL Database using Azure CLI
  4. Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
  5. Configure ODBC Drivers for Oracle 19c
168 Views