In general, the file extensions that we use for SQL Server data files are MDF (Primary Data File), NDF (Secondary Data File - this is optional), and LDF (Transaction Log File). SQL Server works fine with any file extension but Microsoft recommends we use MDF, NDF and LDF. 

In the case where a primary data file is created with a different file extension or accidently renamed to a different file extension, trying to revert the changes without following the proper steps may cause the database to change to a 'Recovery Pending' state. In that case, this article has the steps needed to bring the database back to an 'ONLINE' status.

In this scenario, we have a database 'SSCTest' whose primary data file extension was renamed to .mp3 instead of .mdf. The dataase is actually healthy, online, and accessible, but we are trying to get the extension corrected to .mdf.

Step 1 - Get the Database Properties

Let us first get the properties of the database. Run sp_helpdb and look for the file extension under Results panel.

Step 2: Taking the Database Offline

Now, take the database OFFLINE using the below query.

ALTER DATABASE [SSCTest] SET OFFLINE
GO

Step 3: Changing the File Extension

Change the File Extension of the respective database using query below. 

ALTER DATABASE [SSCTest] 
MODIFY FILE (name = 'SSCTest', filename = 'F:\DATA\SSCTest.mdf')
GO

The result is:

Step 4: Renaming the extension of Physical File.

Before we bring the database 'SSCTest' online, we will have to rename the extension of the physical file in the path. The below images the status of the data file extension before and after renaming the file extension.

Before: 

After:

Step 5: Bringing the Database Online

Bring the database online using the below query and recheck for the database file extension by checking properties of the database running sp_helpdb under Query Aanalyzer.

ALTER DATABASE [SSCTest] SET ONLINE
GO

Result:

This resolves the issue with the file extension and the database remains healthy and accessible as earlier.