Tip: Rename a live production database.

 

I had a requirement from application team that I should rename a database when the application is running and the transactions were happening. The reason was that they didn’t want any outages registered for the entire application. If they stop the services, it would impact the the whole application.

Steps

1. Set the target database into Single user mode.
2. Rename database.
3. Bring the database back into Multi user mode.

Use below query to perform above steps.

USE targetdatabase
GO
/*Enable Single user mode killing all connections*/
ALTER DATABASE targetdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Rename targetdatabase to targetdatabaseNewName*/
ALTER DATABASE targetdatabase MODIFY NAME = targetdatabaseNewName
GO
ALTER DATABASE targetdatabaseNewName SET MULTI_USER
GO

Here you go !. You are done.