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:

• The multiple connections of SSMS


• Slow SSMS load times – improving SSMS launch performace in 2 simple steps


• “Disconnect All Queries” feature of the SSMS query editor


• General SSMS troubleshooting

Until we meet next time,

Be courteous. Drive responsibly.