#0251 – SQL Server – Script to rename a database


Recently, one of my colleagues asked me a seemingly simple question:



I am facing issues in renaming a database – can you give me a script that can help me?


I was quite surprised at the question, but later realized why the issue was encountered. What had happened was that on our development servers, a database was restored, some data-cleanup work done and then the team wanted to run some pre-defined scripts on the database for checking and comparison purposes. These scripts were hard-coded to use a particular set of database names and therefore, the team wanted to rename the given SQL Server database.


However, what most of us don’t realize when working with the SQL Server Management Studio (SSMS) is that it opens up multiple connections to a given database. In this particular case also, the query editor’s connection was open which was causing issues with database renaming.


I therefore provided the team with the following script, which essentially takes the database into single-user mode and then renames the database to avoid any issues being caused by multiple open connections.

–Important: This changes the database context to master.
–If the user database remains in use, the rename will not succeed
USE master;
GO
–Now, take the DB in single-user mode
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER;
GO
–Now, rename the DB
USE master;
GO
ALTER DATABASE AdventureWorks2008R2 MODIFY NAME = MyAdWrks2008R2;
GO
–Now, bring the database back to multi-user mode
–Caution: Use the new name here because the database would have already been renamed
USE master;
GO
ALTER DATABASE MyAdWrks2008R2 SET MULTI_USER;
GO

–Do not forget to apply necessary permissions again


Further Reading:


In the same context of SSMS and related connections, some of my previous posts might interest you:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

One thought on “#0251 – SQL Server – Script to rename a database

  1. dishdy

    I use single user mode also for restoring databases. In some testing/development environments you have to restore a database many times during a day. So you can’t waste time finding out who/what is connected. You just disconnect everything… and restore.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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