SQL Server Rename Database along with Files

In this article, we will show you, How to Rename SQL Server Database along with Files (mdf, and ldf). For this demonstration, we created a database OldNameDB.

Below T-SQL query will show you the Logical File names, and the Physical location of the database

USE master
GO

SELECT name, physical_name, type_desc, state_desc, size 
FROM sys.master_files
WHERE database_id = DB_ID(N'NewNameDB')
GO

OUTPUT

SQL Server Rename Database

Generally, we use sp_renamedb stored procedure to rename the db. But, this will only change the name that appear over here.

EXEC sp_renamedb 'NewNameDB', 'OldNameDB'

OUTPUT

You can see the new name under the Object explorer

Let me execute the below query

USE master
GO

SELECT name, physical_name, type_desc, state_desc, size 
FROM sys.master_files
WHERE database_id = DB_ID(N'NewNameDB')
GO

You can see from the Output sp_renamedb hasn’t changed the database Logical Name, or the Files that represent the database.

Let me rename the Database to OldNameDB, and perform the following operations in the specified manner.

SQL Server Rename Database along with Files

In SQL Server Rename database along with files involves in modifying the mdd and ldd file names. Below statement will modify the Logical names of the Database.

-- Set the database to Single User
ALTER DATABASE OldNameDB 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE OldNameDB 
MODIFY FILE (NAME = N'OldNameDB', NEWNAME = N'NewNameDB')
GO

ALTER DATABASE OldNameDB 
MODIFY FILE (NAME = N'OldNameDB_log', NEWNAME = N'NewNameDB_log')
GO

OUTPUT

Below statement will detach the OldNameDB Database. After detaching the database, you can modify the File names in the physical location

USE master
GO

EXEC sp_detach_db @dbname = N'OldNameDB'
GO

OUTPUT

Navigate yourself to the location of the database

As you can see from the below screenshot, we renamed the mdf, and ldf names

Below statement will create a new database called NewNameDB using the File names that we changed before

-- SQL Server Rename Database along with Files Example
USE master
GO

CREATE DATABASE NewNameDB ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\NewNameDB.mdf'),
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\NewNameDB_log.ldf') 
FOR ATTACH
GO

OUTPUT

Now you can see the database with new name

Let me see the Logical File name, and the files

USE master
GO

SELECT name, physical_name, type_desc, state_desc, size 
FROM sys.master_files
WHERE database_id = DB_ID(N'NewNameDB')
GO

OUTPUT

I think, I forgot to set the database to Multi-user

ALTER DATABASE NewNameDB SET MULTI_USER
GO

OUTPUT