SQL Server Rename Database along with Files

This section shows you how to Rename Database in Sql Server along with Files (MDF, and LDF). For this SQL Server Rename Database demonstration, we created a database OldNameDB.

Below SQL Server 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

SQL Server Rename Database

Generally, we use a sp_renamedb stored procedure to rename the Database. However, this will only change the name that appears over here.

EXEC sp_renamedb 'NewNameDB', 'OldNameDB'

Messages
--------
The database name 'NewNameDB' has been set.

You can see the new name under the SQL Server Object Explorer

Let me execute the below database 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 Logical database Name or the Files that represent the Database.

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

Rename Database along with Files

In SQL Server Rename database along with files involves in modifying the MDF and LDF file names. It 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

It 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

Messages
--------
Commands completed successfully.

Navigate yourself to the location of the database

we renamed the MDF, and LDF names

it will create a new Database in SQL Server called NewNameDB using the File names that we changed before

USE master
GO

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

Messages
--------
Commands completed successfully.

Now you can see the database with a 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

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

ALTER DATABASE NewNameDB SET MULTI_USER
GO

Messages
--------
Commands completed successfully.