Saturday, December 20, 2008

Rename SQL 2005 Database - Error: The database could not be exclusively locked to perform the operation.

During database restore, I tried to rename existing database in SQL Server 2005 by simply trying following command:
ALTER DATABASE mydatabase MODIFY NAME = mydatabase_OLD

But it didn't work and gave following error:
"The database could not be exclusively locked to perform the operation."

In order to fix, I had to set it to single user for a while, change the name and then set it back to multi-user.

I had to execute following commands to make it work:

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE mydatabase MODIFY NAME = mydatabase_OLD
GO
ALTER DATABASE mydatabase_OLD SET MULTI_USER
GO

1 comment:

Feel free to give constructive feedback or let me know if it was helpful.