SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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.
*/
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.