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

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
Previous Post
SQL SERVER – Download RML Utilities for SQL Server
Next Post
SQL SERVER – Reverse String Word By Word – Part 2

Related Posts

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.

    Reply
  • 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
  • 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
    • 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.

      Reply

Leave a Reply