Creating Database and Tables - Beginning C# 5.0 Databases - page 25

Figure 3-2. SQL Server user databases
Moving onward, the databases you create will be user databases and listed after the System
Databases folder. It is worth mentioning here that there is no way you can create your own system
database or create a database and put it in the System Databases folder.
The Architecture of a SQL Server Database
SQL Server 2012 databases consist of data and log information. This data and log information is stored in
individual files.
• Primary data file : This is the main file that constructs a SQL Server database,
because it points to other files in the database. The required file extension for this
file is .mdf , and a SQL Server database can have only one primary data file.
• Log data files : This is the recovery file, which stores all the log information that is
used to recover a database in case of failure. The required file extension for this file
is .ldf . Any database must consist of at least one log file, but there can be more
than one log file in a database.
 Note There is a third type of file as well, known as the secondary data file , that is also used to store data
information, pointed to by primary data file. A database can have multiple data files, and the required extension for
a data file is .ndf . Because most of the industrial databases are made of primary and log files, I am not including
more details about the secondary one and instead will focus on primary and log files. You may want to learn more
about it at http://msdn.microsoft.com .
The fact that each database consists of .mdf and .ldf files applies to system databases as well as user
databases. To see this, go to your SQL Server folder under Program Files (see Chapter 1 for information
about how to find your SQL Server folder path); once you are at your SQL Server location, you will see
the database file. In my case, my SQL folder path shows me the files in Figure 3-3.