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.

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

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

Finally, we will take the database online.

ALTER DATABASE [SQLAuthorityCom] SET ONLINE
GO

That’s it – we are done!

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