Every now and then I receive a request to rename a database on a SQL Instance. Renaming a database is straightforward however an issue can arise where users are connected to the database which will prevent you from renaming the database. If it is good practice to notify the users that this will cause the users from getting disconnected from the database.
Needless to say if you are running a 24/7 operation then it would be advisable to take a downtime before proceeding with such a task. Setting the database to single user mode will cause all users to be disconnected from the database. This could be problematic if there are any long running processes running that is crucial to business operations.
Hope this SQL Script to rename a database helps.
---Rename a database
USE master
GO
ALTER DATABASE <current_db_name>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb '<current_db_name>','<new_db_name>'
GO
ALTER DATABASE <new_db_name>
SET MULTI_USER
GO