How to Rename a SQL Server Database?

Read time 5 minutes

Renaming an SQL database is a task that administrators must handle as part of their responsibilities. This can be accomplished through SQL Server Management Studio or Transact-SQL cmdlets. In this guide, we’ll demonstrate the step-by-step process for you.

At times, SQL administrators must rename databases for administrative or technical purposes. Renaming a SQL Server Database is straightforward and can be accomplished in multiple ways. However, it’s crucial to understand key aspects before initiating the renaming process.

In this article, we will provide you with a step-by-step guide on renaming a SQL Server Database correctly, ensuring a smooth process without any disruptions.

Prerequisites

Before renaming the SQL Server Database, it’s important to consider the limitations, restrictions, and security permissions associated with the task.

To rename a SQL Server Database, you have the option to use either SQL Server Management Studio or Transact-SQL. Both methods are explained in detail below.

Includes Set Database to Single-User Mode:

• Using SQL Server Management Studio
• Using Transact-SQL

Rename SQL Server Database

• Using SQL Server Management Studio
• Using Transact-SQL
• Kernel SQL Database Recovery
• Conclusion

Limitations & Restrictions

Below are the limitations & restrictions associated with the task of renaming a SQL Server database:

1. If you’re renaming a database in Azure SQL Database, it is important to confirm that no users are connected to that database.
2. Renaming a system database is not permitted.
3. Database renaming is not allowed when users are accessing the SQL Server database.For closing any open connection to the database, you can set the database in single-user mode.

Security Permissions

For performing specific tasks on the SQL Server database, the user should be assigned with certain permissions. And for renaming, the SQL Server database name ALTER permission is required.

Set Database to Single – User Mode

To ensure no active connections and prevent other users from accessing the database during the renaming process, you can switch the database to single-user mode.

Make sure of the prerequisites mentioned below before proceeding to set the database to single-user mode:

Confirm that AUTO_UPDATE_STATISTICS_ASYNC option is set OFF. When the option is enabled, accessing the database in single-user mode is restricted, as a background thread used for updating statistics establishes a connection with the database.

Using SQL Server Management Studio

Follow the steps given below to set database in Single-User mode using Server Management Studio:

1. Open Object Explorer and connect and expand an instance of SQL Server Database Engine.
2. Select the database, right-click, and click on Properties.
3. In Database Properties, click on the Options page.
4. Select Single, from the Restrict Access option.
5. If other users are connected with the database, Open Connections message will appear.For changing the property, close all other connections and click Yes.

Done, you’ve set the database to single-user mode using SQL Server Management Studio successfully.

Using Transact-SQL

Follow the below steps to set the database in single-user mode using Transact-SQL:

1. Establish a connection with the Database Engine.
2. Select New Query on the standard bar.
3. Type the code in given below syntax in the query window and click Execute.

You’ve successfully set the database in single-user mode using Transact-SQL.

Rename SQL Server Database

Using SQL Server Management Studio

With a clear understanding of limitations, restrictions, and security permissions, and after setting the database to single-user mode to disconnect other users, you can proceed to the renaming stage.

Steps given below can help you to rename a SQL Server or Azure SQL Server using SQL Server Management Studio:

1. Open Object Explorer and establish connection with a SQL instance.
2. Again, confirm that no other users are connected to the database, else use the above method to set the SQL Server Database in Single-User mode.
3. In Object Explorer, expand Databases.
4. Select the database you wish to rename, right-click on it, and click Rename.
5. Assign the database with a new name and click Ok.

You’ve renamed the SQL Server database successfully using SQL Server Management Studio.

Using Transact-SQL

This approach not only guides you through renaming the SQL Server Database using T-SQL but also helps you switch the database to single-user mode, perform the renaming, and then revert it back to multi-user mode afterward.
Follow below steps:

1. For your instance, establish a connection with the master database.
2. Launch a query window.
3. Type the code (in the given syntax) in the query window and click Execute.

Database renaming has been done successfully, and the process included setting the database in single-user mode and upon database renaming, reverting it to multi-user mode using Transact-SQL.

Kernel SQL Database Recovery

In certain instances, SQL Database files (MDF/NDF) might become corrupted or inaccessible, putting essential data, including customer information, at risk of loss. Retrieving this data is time-consuming and detrimental to business. These situations are highly critical, demanding an immediate and effective solution.

Kernel for SQL Database Recovery is the best find you can have for this purpose. It recovers large size database file(s) – both MDF/NDF, creates the backup script of restored databases, recover multiple database file at once, etc.

The tool offers support for different versions of Microsoft SQL Server – 2019, 2016, 2014, 2012, 2008, etc. It doesn’t matter which version of SQL Server you’re using on your system, download the tool from the website, install it and jump on using the software.

Conclusion

Renaming a SQL Server Database demands a thorough understanding of its associated steps. Without precise knowledge, mistakes are likely. In the event of errors, this tool serves as your comprehensive solution, ensuring a smooth resolution.