Rename logical and physical names of database files in SQL Server

To find the current logical and physical database file names:

USE Database_name
EXEC sp_helpfile

or

USE master
GO
SELECT
name AS [Logical_name],
physical_name AS [File_Path],
type_desc AS [File_Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'Database_name')
GO

To rename the Logical names of database files:

ALTER DATABASE [Database_name] MODIFY FILE (NAME=N'Logical_file_name', NEWNAME=N'Logical_file_name_new')
GO
ALTER DATABASE [Database_name] MODIFY FILE (NAME=N'Logical_file_name_log', NEWNAME=N'Logical_file_name_log_new')
GO

Or using SQL Server Management Studio:
Right-click Database -> Click Properties -> Click Files
Rename Logical Names and click OK:

To rename the physical names of database files:

put the database in single user mode, detach the database, then rename the physical files:

ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'Database_name'
GO

Rename the physical files.
After you renamed the physical files, attach the database, using this script:

USE [master]
GO
CREATE DATABASE Database_name ON
( FILENAME = N'D:\DATA\Database_file_name.mdf' ),
( FILENAME = N'D:\DATA\Database_file_name_log.ldf' )
FOR ATTACH
GO

Put database in multi user mode again:

ALTER DATABASE Database_name SET MULTI_USER
GO

Or using SQL Server Management Studio:
Right-click Database -> Click Tasks -> Click Detach...

Rename the physical files.
After you renamed the physical files, attach the database:

Right-click Database -> Click Tasks -> Click Attach...

If the post helped you, please share it:
Pin It

5 comments to Rename logical and physical names of database files in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">