SQL Server # TSQL to Rename Database Name along with its Logical and Physical Name

There are many ways to rename the SQL server Database name. Today, I will explain how to rename a Database in SQL Server using T-SQL along with its Logical and Physical Name.

The very easiest and most popular way of renaming is – Suppose, you have a database named as “TestDB”. Now you want to rename it with “NewTestDB”.

  --Rename the data base "TestDB to New_TestDB".

  EXEC sp_renameDB 'TestDB','New_TestDB'

  GO

Above given example just renames the Database title (name); However if you want to rename the physical file, just Detach the database, Go to the file path which is currently located, rename both the LDF and MDF file, and now here Attach the database.

Below steps are given for renaming the database by using the TSQL statement – Just open SSMS Copy the below scripts and press F5.

USE TestDB

GO

è First of all set the database for a single user, it will disconnect all the currently connected users.

ALTER DATABASE TestDB

  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

è Now rename the Database title (Name).

EXEC master..sp_renamedb 'TestDB','NewTestDB'

GO

è Now the change the Logical file name, because above statement just change the name of the Database.

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB', NEWNAME=N'NewTestDB')

GO

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB_log', NEWNAME=N'NewTestDB_log')

GO

è Now Detach the Database for change the Physical file name, as we can't do anything with the physical file when the database is in active mode.

EXEC master.dbo.sp_detach_db @dbname = N'NewTestDB'

GO

è Now Change the SSMS settings if you haven’t set it.

è This is necessary because the below provided command will not run without set this.

sp_configure 'show advanced options'

GO

/* 0 = Disabled , 1 = Enabled */

sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE WITH OVERRIDE

GO

è Now rename your physical files with the use of command.

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB.mdf", "NewTestDB.mdf"'

GO

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB_log.ldf", "NewTestDB_log.ldf"'

GO

è Now just attach the database. (Currently detached by us).

CREATE DATABASE NewTestDB ON

( FILENAME = N'F:\CD\Temporary Work\NewTestDB.mdf' ),

( FILENAME = N'F:\CD\Temporary Work\NewTestDB_log.ldf' )

FOR ATTACH

GO

è Now set the database as multi users can work on this.

ALTER DATABASE NewTestDB SET MULTI_USER

GO

è Finally you can see that, the physical files are renamed.

SELECT

  name AS [Logical Name],

  physical_name AS [DB File Path],

  type_desc AS [File Type],

  state_desc AS [State]

FROM

  sys.master_files

WHERE

  database_id = DB_ID(N'NewTestDB')

GO

Conclusion

Hence, we have learnt how we can rename the SQL Server Database along with its Logical & Physical using some simple TSQL statements.