How to Rename a Database in SQL Server


Rename a Database using T-SQL:

If connections are open to Database you can use below script to kill the connections and then rename.

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

 

Rename DB by using TSQL Script


USE master;

GO

ALTER DATABASE [Sales]

Modify Name = Sales1

GO

 

Comments