SQL Server / Renaming Databases With Active Connections

hello-my-name-isI believe there are many methods and opinions that exist on the best practice to rename a SQL Server DB. Below I’ll quickly give you the method I use to rename a database. In the below example, I rename two DB’s – a common scenario if you’re swapping your production database with a different version:

-- #1 change first db name
use master
-- set db to single user
ALTER DATABASE myProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myProdDB MODIFY NAME = [myOldProdDB]
-- set back to multi user
ALTER DATABASE myOldProdDB SET MULTI_USER

-- #2 change second db name
use master
-- set db to single user
ALTER DATABASE myNewProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myNewProdDB MODIFY NAME = [myProdDB]
-- set back to multi user
ALTER DATABASE myProdDB SET MULTI_USER

Note: this does not change the database or log file names, just the database name.

SQL Server / Renaming Databases With Active Connections

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s