SQL SERVER – Can Database Primary File Have Any Other Extention Than MDF

December 14, 2014

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

SQL SERVER - Can Database Primary File Have Any Other Extention Than MDF pdfrestore

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)

SQL Backup and Restore, SQL Scripts

SQL SERVER – Download RML Utilities for SQL Server

SQL SERVER – Reverse String Word By Word – Part 2

7 Comments. Leave new

Fahad
December 14, 2014 5:59 pm
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.
Reply
Ian Stirk
December 15, 2014 12:52 pm
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
Reply
Viren
December 15, 2014 10:34 pm
I am not sure what is the purpose of allowing other customized extensions, seems like a bug to me!
Reply
Swapna Chowdary
December 16, 2014 5:49 am
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?
Reply