Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Databases»SQL Server»How to Rename a SQL Server Database

    How to Rename a SQL Server Database

    RahulBy RahulJune 21, 20213 Mins ReadUpdated:June 21, 2021

    The Microsoft SQL Server provides you multiple ways to rename a database. You can either use SQL Server management studio or run SQL query to rename a database. Before renaming a database, remember it can break the connections for services configured with the current database name or existing stored procedures.

    How to Rename a SQL Server Database

    In this guide, we will discuss three ways to rename a database in the SQL server. The first option will use SQL query to rename a database, the second will use a stored procedure and the third option will use SQL Server Management Studio (SSMS) to rename a database.

    Use one of the below options’s to rename a SQL server database:

    1. Rename SQL Database with Query

    Use ALTER DATABASE query to rename a database in SQL Server. The renaming process will fail, in case there are any active connections established by applications or other databases.

    Use the below queries to rename the database with SQL query. The first query will change the database to single-user mode to prevent any external connection. After that rename, the database and again set the database to multi-user mode.

    ALTER DATABASE Test_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    
    ALTER DATABASE [Test_db] MODIFY NAME = [Test_db2]
    GO 
    
    ALTER DATABASE Test_db_new SET MULTI_USER;
    GO
    

    That’s it. You will see that database is renamed.

    2. Rename SQL Database with Stored Procedure

    The SQL server will also provide a stored procedure to rename a database. You can follow the same way as above to switch the database to single-user mode and rename the database.

    The database rename queries will be like:

    ALTER DATABASE Test_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    
    EXEC sp_renamedb 'Test_db', 'Test_db2'
    GO
    
    ALTER DATABASE Test_db_new SET MULTI_USER;
    GO
    

    3. Rename SQL Database with Management Studio

    You can also rename the SQL server database similar to renaming a folder in Windows. Open SQL Server Management Studio (SSMS) and connect to the database server. Follow the below steps to change SQL server to single user mode and rename a database.

    First, we will set the database connection to single-user mode to close all active connections to the database before renaming. So follow the below steps to rename the SQL database with SSMS.

    The steps are:

    1. Open Object Explorer in SSMS.
    2. Right Click on the database and click on properties.
    3. In the Properties, select the Options page
    4. From the Restrict Access option, select Single.
    5. Again, right-click the Database and click on Rename.
    6. Enter a new name for database and hit enter.
    7. Again, open Options page in properties and change Restrict Access to MULTI_USER

    Here is the screenshots for the above steps:

    Open the SQL Server database properties:

    Open SQL Server Properties

    Restrict database access to SINGLE_USER in SQL Server.

    SQL Server restrict access to single user

    Select rename option in SQL server database properties.

    SQL Server Open Rename Option

    Finally, rename the SQL Server database.

    Rename SQL Server Database

    Conclusion

    This tutorial helped you with 3 methods to rename a database in the SQL Server on the Windows system.

    database SQL Query SQL SErver SSMS
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleWget Command in Linux with Examples
    Next Article How To Restore SQL Server Database

    Related Posts

    How to Install MariaDB on Ubuntu 22.04

    Updated:May 28, 20222 Mins Read

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How To Install MariaDB on Debian 11

    4 Mins Read

    How to Install Redis on Debian 11 Linux

    Updated:September 16, 20213 Mins Read

    How to Check the PostgreSQL Version

    Updated:August 6, 20212 Mins Read

    How to Rename MySQL Database

    Updated:July 26, 20213 Mins Read

    Leave A Reply Cancel Reply

    Recent Posts
    • How to run “npm start” through docker
    • Filesystem Hierarchy Structure (FHS) in Linux
    • How to accept user input in Python
    • What is difference between var, let and const in JavaScript?
    • What is CPU? – Definition, Types and Parts
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.