How to Rename Dabase Name using T-SQL sp_rename in SQL Server 2008
In order to rename database name in SQL Server, sp_renamedb stored procedure can be used.
Below you can find a sample sql script demonstrating how sp_renamedb can be used to rename SQL Server database.
CREATE DATABASE SQLDatabase
GO
sp_renamedb @dbname = 'SQLDatabase' , @newname = 'RenameDatabase'
GO
Not only for Microsoft SQL Server 2008 databases, database administrators and sql developers can use sp_rename to rename SQL Server 2005 databases too.
Of course if database is in use, it may not be possible to rename database.
Let's create a sample case where we can not rename database using sp_rename since the database is in multi-user mode.
Now open a new session which will lead multi-user mode for the SQL Server test database.
sp_renamedb @dbname = 'RenameDatabase' , @newname = 'NewDBName'
/*
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
*/
In order to resolve Msg 5030 - The database could not be exclusively locked to perform the operation error, you should change the SQL Server database mode from multi-user mode to single-user mode.
Please refer to sql tutorial How to Alter a SQL Server Database as Single User Mode and as Multi User Mode for alternative methods.