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

The below 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 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 representing the Database.

Let me rename the Database to OldNameDB and perform the following operations in a specified manner.
Rename the Database along with the Files
In SQL Server rename database along with files involves modifying the MDF and LDF file names. It will modify the Logical names of the Database.
-- Set the db 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 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.