Rename a Database in SQL Server

128 views 05:09 0 Comments 11 October 2019

Introduction

We can rename a user-defined database in SQL Server by using SQL Server Management Studio or Transact-SQL. System databases cannot be renamed. The database name cannot be changed while it is online and other users are accessing the database. We can set a database in single-user mode to close any open connections.

Set the database to single-user mode

By TSQL

USE master
GO
ALTER DATABASE Our_Tech_Ideas SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

By SSMS

Right-click on Database > Properties

Option > Other options > Restrict access > SINGLE_USER

Rename SQL Server database using T-SQL

This command is useful for SQL server 2005, 2008, 2008R2, 2012 and onward

ALTER DATABASE Our_Tech_Ideas MODIFY NAME = Our_Tech_Ideas_new

Or

EXEC sp_renamedb 'Our_Tech_Ideas' , 'Our_Tech_Ideas_new'

By SSMS

Set the database to multi-user mode

By TSQL

ALTER DATABASE Our_Tech_Ideas SET MULTI_USER
GO

By SSMS

Right-click on Database > Properties > Option > Other options > Restrict access > MULTI_USER

Leave a Reply

Your email address will not be published. Required fields are marked *