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.
Free SQL Comparison tools
Trusted by thousands of users
Download your copy now
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.
|