How to rename a SQL Server Database?

Yatendra Singh Yatendra Singh Updated On - 28 Jan 2019

Renaming a SQL Server Database is not tricky, you can do it in a couple of ways, but before actually renaming the database, there are a couple of things which you should know.

In this article, we’ll guide you on how to rename a SQL Server Database the right way without messing with anything.

Prerequisites

Things to consider before renaming the SQL Server Database are Limitations & Restrictions and Security Permissions associated with the task.

For renaming a SQL Server Database, you can either use SQL Server Management Studio or Transact-SQL, and we’ve covered both methods below.

Includes
Set Database to Single-User Mode:

Rename SQL Server Database

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

For closing any open connections and to stop other users from connecting to the database while the process of renaming is going on, you can set 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. If the option is set to ON, you can’t access the database in single-user mode (as the background thread used to update 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.


USE master;
GO
ALTER DATABASE
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE
SET READ_ONLY;
GO
ALTER DATABASE
SET MULTI_USER;
GO

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

Rename SQL Server Database

Using SQL Server Management Studio

Now that you’ve understood the limitations & restrictions and security permissions and have set the database in single-user mode to disconnect other users connected to the database, you can now 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 method not only will help you in renaming the SQL Server Database using T-SQL, it will also assist you to set the database in single-user mode, and after renaming, place database back in multi-user mode.
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.


USE master;
GO

ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MODIFY NAME = ;
GO

ALTER DATABASE SET MULTI_USER
GO

Database renaming has processed 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

There may be times when the SQL Database file(s) – MDF/NDF, become corrupt or inaccessible and all your crucial data would be at high risk of data loss, that data could be of your customers, and gathering all the information again would waste lots of hours. This won’t be good for business. Such situations are very critical, as finding the right solution becomes necessary.

Kernel SQL Database Recovery is the best find you can have on the Internet; its repair and recovery features outperform other recovery software. Some examples are recover large size database file(s) – both MDF/NDF, create the backup script of restored databases, recover multiple database file at once, etc.

SQL Database Recovery tool offers support for different versions of Microsoft SQL Server – 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 the SQL Server Database requires proper acknowledgment of all the measures associated with it; so, without learning the process specifics, it is possible that you may mess up. And in case if something goes wrong, which we hope not to, then, Kernel SQL Database Recovery tool is your one-stop solution.