1. We will check the physical database file names by running the below T-SQL code to validate the database creation.

USE Manvendra
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files

2. Our next step is to rename the physical database file names. The physical database file names cannot be changed or renamed while the corresponding database is ONLINE because these files will be in use by SQL Server, so we need to bring our database OFFLINE.

USE [master];
GO
--Disconnect all existing session.
ALTER DATABASE Manvendra SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Change database in to OFFLINE mode.
ALTER DATABASE Manvendra SET OFFLINE

3. Since the database is OFFLINE we can rename the physical database file names of the target database.  The picture below shows we have renamed all associated database files and added the suffix „Renamed” to easily identify the files.

https://www.mssqltips.com/tipimages2/4419_Rename_files.JPG

4. Once the physical database files have been renamed, the next step is to update the system catalog where the older file name is present. Run the below ALTER statements for each file to be renamed separately. I am renaming all four files, so I will run four ALTER statements.

ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_Renamed.mdf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_1', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_1_Renamed.ndf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_2', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_2_Renamed.ndf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_log', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_log_Renamed.ldf')
GO

5. Once the system catalog is updated the next step is to bring the database ONLINE. Run the below T-SQL statement to bring the target database ONLINE. You can use SQL Server Management Studio as well to bring the database ONLINE.

ALTER DATABASE Manvendra SET ONLINE
Go
ALTER DATABASE Manvendra SET MULTI_USER
Go

6. The final step of this tip is to validate the logical and physical file name changes which we worked on in this tip. We will run same command which we ran in step 2 to get the physical database file names of the target database.

USE Manvendra
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files

Remarks:

• Do not change physical database file names of your production database until it is required or needed.
• First test this process in to lower life cycle and then replicate the change in to production post approval process.
• After restoring databases with a different name, use this tip to change the physical file names.