Rename a Database in SQL Server
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
|
1 2 3 4 |
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
|
1 |
ALTER DATABASE Our_Tech_Ideas MODIFY NAME = Our_Tech_Ideas_new |
Or
|
1 |
EXEC sp_renamedb 'Our_Tech_Ideas' , 'Our_Tech_Ideas_new' |
By SSMS
Set the database to multi-user mode
By TSQL
|
1 2 |
ALTER DATABASE Our_Tech_Ideas SET MULTI_USER GO |
By SSMS
Right-click on Database > Properties > Option > Other options > Restrict access > MULTI_USER
