Microsoft SQL Server has released a variety of database editions. Each edition is focused on different needs. SQL Server has server editions and specialized editions for light weight needs to heavy enterprise needs, however all editions have one thing in common, similar file structure. When installed, Microsoft SQL Server stores default data file types in various directories on the respective computer. Master Database Files (MDF) and Log Database Files (LDF) are the primary files created for each database in the SQL Server environment.
Default installation
Video of the Day
During the initial installation, each SQL Server edition will place default system MDF and LDF files corresponding with their respective function, not covered in this discussion. A Master, MSDB, Model, TempDB, and Resource files are generated and stored in the default install directory defined during installation. Each core system file is used for specific requirements, but are databases, storing information correlating to their respective job.
Video of the Day
Creating User Databases
User databases are created for specific uses by an application or user. When a database administrator creates a new database, SQL Server creates both the MDF and LDF file corresponding to that database. SQL Server initially places them in it's default directory, or the administrator can define where they are to be stored. After creation, they can be moved to different locations for convenience, performance and storage concerns.
Master Database File
MDF's are where all the database information is stored. It contains the rows, columns, fields and data created by an application or user. Database column creation, modifications and information record creation, modification are all stored within this file for searching and application use.
Log Database File
During various creation and modification process's within the MDF, all activity is logged in the LDF. As dynamic information is being processed in and out of memory, called transactions, data is stored in the LDF for error management. Consequently, all user activity is recorded for reference.
Using MDF’s and LDF’s
Database administrators can do a variety of things with MDF's and LDF's. Once created, they can be moved by detaching the database from the instance, moving the physical file through the explorer and reattaching. MDF's and LDF's can be moved to different directories on different file storage devices using the detach and attach functions. MDF's also store what edition created the file and can become detached from a SQL Server instance for unknown reasons. In certain situations, MDF's can be imported into a new blank database for manipulation.
Putting It All Together
Microsoft SQL Server is a complex database environment which has many facets, components and supporting services which are not all covered in this basic file type explanation. SQL Server uses the two core file types, MDF and LDF for the majority of it's work. These are the to most important files within the SQL Server environment.