I have created a sample database called TestDB. After that, I ran this select:

select * from sys.master_files

I found my .MDF, .LDF files from this select. Then I want to find out the .NDF file location . Is it possible to see those files like .NDF, .MDF , .LDF?

2 Answers 2


NDF is secondary data file, By default it will not be created unless we create the file while creating SQL server database or manually create it after database is created using AlTER DATABASE statement. SQL server by default will create one primary data file and one log file. If it is not visible in sys.master_files means you have not created it.

Read more about database files and filegroups


To add secondary data file please see


  • Literally its not possible to open mdf,ldf file you can do it using notepad but it will contain all grabage value. You can attach mdf and ldf file to database using SP_Attach_db command:msdn.microsoft.com/en-us/library/ms179877.aspx and then can use select statements to read data from table. You can also use undocumeted fn_dblog command to read contents of log file but this is not supported by Microsoft.
    – Shanky
    Jul 15, 2014 at 9:48

The ndf file is an additional file, which you can add to your database in the database properties. You have to think about files and filegroups as well. Otherwise you are not able to control, what is stored in which file. Multiple files can be used mainly to distribute load on your IO system, allow you to implement partial backup strategy for large databases etc.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.