[MSSQL 2012] Rename Database .mdf and .ldf File Names

There are a couple of ways you can achieve this which includes:

1. While restoring the database
2. After restoring the database

Lets take a look at each.

While Restoring the Database

Create a new database by right click Databases in the Object Explorer > New Databases. Name the database <dbName>, and press OK, This will create two files as <dbName>.mdf and <dbName_log>.ldf representing both mdf and ldf files respectively.

From Object Explorer, right click the <dbName> and select Tasks > Restore > Database.

General Tab: 

• select the Source as existing Database or from some backup from device.
• from the Destination database, select the <dbName>

Options Tab:

• Check Overwrite the Existing Database (WITH REPLACE) - because we're using the newly created database.

Files Tab:

• For Row Data file type, select ... from 'Restore As' column and from open dialog, select <dbName>.mdf, or type in the name you like
• For Log file type, select ... from 'Restore As' column and from open dialog, select <dbName_log>.ldf or type in the name you like

Press OK. This will restore the database using the file name of your choice.

NOTE: For future restoration, you may required to rename the logical name of the database. To do this, right click the <dbName> and select Properties. On Files tab, under Database Files, rename the logical names for both Row Data and Log types to <dbName> and <dbName_log>, if the names are different from your selected database.

After Restoring the Database

For <dbName> database, if it is restored with different file name <diffName>.mdf and <diffName>_log.ldf and would like to rename it as <dbName>.mdf and <dbName>.ldf, we would do the following:

--1# View Database file types and relevant physical file name with absolute path

SELECT      name, physical_name

FROM        <dbName>.sys.database_files;

We'll use the retrieved names to rename the files. Lets say the logical names are <diffLogicalName> and <diffLogicalName>_log

--2# Turn the database offline

ALTER DATABASE <dbName> SET OFFLINE;

3# Now from windows explorer, navigate to actual physical file location and rename the files manually.

--4# Modify the file name with absolute path

ALTER DATABASE <dbName>

MODIFY FILE (NAME = <diffLogicalName>, 

FILENAME = 'D:\...\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<dbName>.mdf')

GO

ALTER DATABASE <dbName>

MODIFY FILE (NAME = <diffLogicalName>_log, 

FILENAME = 'D:\...\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<dbName>_log.ldf')

GO

--5# Turn the database offline

ALTER DATABASE <dbName> SET ONLINE;

This will rename the files of your choice.

Enjoy!