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.

SQL Server Rename Database along with Files 1

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 along with Files 2

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'

Output

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

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

SQL Server Rename Database along with Files 4

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.

SQL Server Rename Database along with Files 5

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
SQL Server Rename Database along with Files 6

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

Output

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

Navigate yourself to the location of the database

SQL Server Rename Database along with Files 8

we renamed the MDF, and LDF names

SQL Server Rename Database along with Files 9

it will create a new Database in SQL Server 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

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

Now you can see the database with a new name

SQL Server Rename Database along with Files 11

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
SQL Server Rename Database along with Files 12

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

ALTER DATABASE NewNameDB SET MULTI_USER
GO

Output

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