How to Rename Database in SQL Server - SQL Server / TSQL Tutorial Part 26

Scenario:

We often face the situation where we need to rename the database. Think about situation, you are working as SQL Server developer for Financial firm and they have the database name TechBrothersIT and they would like to rename to Techbrothers. You need to provide the scripts that can be run in QA, UAT and Production environment.

Solution:

Renaming by GUI:
Renaming database is very easy, you can simply Right Click on Database and then choose Rename.
How to rename Database in SQL Server - SQL Server / TSQL Tutorial


You will see that the name of database will prompt you for change. Go ahead and change it and hit Enter.
How to Rename Database in SQL Server - SQL Server / TSQL Tutorial




How to Rename Database by using TSQL:
You can use TSQL To rename database in SQL Server. 

1) By using Sp_Rename System Stored Procedure
We can use sp_rename system Stored Procedure to rename database in SQL server. Let's say we would like to rename TechBrtohersIT to TechBrother. We can use below script.

sp_renamedb 'OldDatabaseName','NewDatabaseName'


EXEC sp_renamedb 'TechBrothersIT','TechBrothers'

2) Use Alter Database with Modify
We can use below script to rename. In this example, I am renaming TechBrothersIT To TechBrothers.

USE master;
GO
ALTER DATABASE TechBrothersIT
Modify Name = TechBrothers ;
GO

Common Error:
Let's say that the database is in use by different applications. you might get below error.

Msg 5030, Level 16, State 2, Line 4
The database could not be exclusively locked to perform the operation.

In this case, you can kill all connections before you run rename script.  Below script can be used to kill all connections on a database.Make sure with your team and other teams before you kill all connections on database for renaming.

USE MASTER
GO
DECLARE @DatabaseName AS VARCHAR(500)
-->Provide the DataBaseName for which want to Kill all processes.
SET @DatabaseName='YourDataBaseName'
DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
  SELECT spid
  FROM   sys.sysprocesses
  WHERE  DB_NAME(dbid) = @DatabaseName
OPEN KillProcessCur
FETCH Next FROM KillProcessCur INTO @Spid
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END
CLOSE KillProcessCur
DEALLOCATE KillProcessCur






1 comment: