In which Files does SQL Server Actually Store Data?

Database Files

SQL Server databases have three types of files:

Primary data files:- The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file.An MDF file, known as the primary data file, which contains the schema and data. The recommended file name extension for primary data files is .mdf.

Secondary data files:-Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

Log files:- Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf and a LDF file, which contains the logs.

MDF : Which is actual data file storage.

LDF : (Log data files) which stores transaction log.

Location of mdf and ldf file locations by query:

SELECT * FROM sys.database_files