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)
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 .
Hi Pinal,
The last screenshot seems to have been inserted by mistake as it still shows file with .pdf extension instead of .mdf extension.
Fixed the issue as they were reversed. Thanks for bringing to attention.
In which scenarios can we change the extension of the database files?
Why Microsoft didn’t restrict the extension to mdf and ldf?
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 ???