SQL SERVER – How to Rename Extention of MDF File? – A Simple Tutorial

The other day while working with a customer during SQL Server Performance Tuning Practical Workshop, I noticed an MDF file with very strange extensions. When I asked the DBA why they have such a large data file with extension PDF (instead of common MDF file extension), he replied that honestly they have no idea who to rename a data file extension. They had once tried earlier, but that has sent their database into recovery and they had to revert that back immediately.

SQL SERVER - How to Rename Extention of MDF File? - A Simple Tutorial mdfextention0

 

I can totally understand the issue which DBA explained. I explained him the entire procedure to rename their PDF file extension to MDF and promised a follow up blog post. Here is the blog post which I promised to him.

Before you continue reading the blog post, please note that SQL Server just works fine with any extension of data and log file. The extensions are not required to be MDF or LDF. However, the common practice is to keep them MDF and LDF.

Now let us recreate the scenario where we will create a database with the PDF file extension for data files.

CREATE DATABASE [SQLAuthorityCom]
ON PRIMARY
( NAME = N'[SQLAuthorityCom]', FILENAME = N'D:\DATA\SQLAuthorityCom.pdf' )
LOG ON
( NAME = N'[SQLAuthorityCom]_log', FILENAME = N'D:\DATA\SQLAuthorityCom_log.ldf')
GO

SQL SERVER - How to Rename Extention of MDF File? - A Simple Tutorial mdfextention3

Now next we will take them offline and change the file name.

ALTER DATABASE [SQLAuthorityCom] SET OFFLINE
GO
ALTER DATABASE [SQLAuthorityCom] 
MODIFY FILE (name = '[SQLAuthorityCom]', 
			filename = 'D:\DATA\SQLAuthorityCom.mdf')
GO

Next step is to change the file extension in windows explorer from .pdf to .mdf

SQL SERVER - How to Rename Extention of MDF File? - A Simple Tutorial mdfextention2

Finally, we will take the database online.

ALTER DATABASE [SQLAuthorityCom] SET ONLINE
GO

SQL SERVER - How to Rename Extention of MDF File? - A Simple Tutorial mdfextention1

That’s it – we are done!

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – How to Install SQL Server Management Studio (SSMS) From Command Line?
Next Post
SQL SERVER – Alternative to DBCC INPUTBUFFER – sys.dm_exec_input_buffer

Related Posts

5 Comments. Leave new

  • Recent one event occur in my organization attack the ransomware and corrupted MDF ,ndf and ldf file . I have seen ransomware affected c certain kindly of extension witch is one the SQL data file . Then i realize if i changed SQL data file then we can save .

    Reply
  • Hi Pinal,

    The last screenshot seems to have been inserted by mistake as it still shows file with .pdf extension instead of .mdf extension.

    Reply
  • Asrar Ahmad Ehsan
    September 25, 2019 12:37 pm

    In which scenarios can we change the extension of the database files?
    Why Microsoft didn’t restrict the extension to mdf and ldf?

    Reply
  • Hi i have same scenario instead of PDF the LDF file is named as MDF and there now two MDF files and i want to change one MDF file to LDF and im using the above query’s, but its showing file name doesn’t exist ???

    Reply

Leave a Reply

Menu