Here is interesting question I received yesterday. I personally think it is one of the most interesting questions I have received this month.
“Can Database Primary File Have Any Other Extention Than MDF?”
Before you read the answer – think for a moment.
Now read the answer in the next line.
Yes, Of course. MDF file extension of the primary data file of the database is just a normal practice, but essentially, you can use any other database file extension as well.
Here is an example where I am able to attach files with PDF extension to the database. Please note that this file was created by SQL Server earlier and was always used for primary data file.
USE [master] GO CREATE DATABASE [tests] ON ( FILENAME = N'D:\data\tests.pdf' ), ( FILENAME = N'D:\data\tests_log.ldf' ) FOR ATTACH GO
As you can see that other extensions are successfully restored to the database. If you want to practice this example, you can download this example by clicking over here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hey Pinal,
Thanks for the information. Herewith I am attaching the error I am facing while running the query you have mentioned.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘tests’. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database ‘tests’ cannot be opened because it is version 782. This server supports version 655 and earlier. A downgrade path is not supported.
Please suggest.
I think this is because you have an earlier version of SQL Server.
Try with SQL Server 2014.
Thank you Pinal, Yes I am using version 2008 R2..
Let me try with 2014..
Nice article.
Please be careful with using non-standard approaches (like unusual extensions for primary files). Virus checkers often have an exclude list of file types to ignore (e.g. .mdf), specified at the company level. Using a non-standard extension may mean the virus checker checks your files often and slows down you system,
Thanks
Ian
I am not sure what is the purpose of allowing other customized extensions, seems like a bug to me!
This is interesting but how it is useful? can we really use the DB? are we really making our .mdf file in pdf format and using it to create the DB?
No. The question was about any format – not specific to PDF. One should not convert their file to another format and use it.
This particular thing may be useful if you have received file with different format and you know that it is MDF file.