Data structures and transaction logging

We usually think of a database as a physical structure consisting of tables containing columns and rows of data and indexes. However, this is just a human point of view. From SQL Server's perspective, a database is a set of precisely structured files described in the form of metadata, also saved in the same database structures within the database. We are starting this chapter with an explanation of storage internals because a conceptual imagination of how every database works is very helpful when the database needs to be backed up correctly.

How data is stored

Every database on SQL Server must have at least two files:

• The primary data file with the usual suffix, mdf
• The transaction log file with the usual suffix, ldf

For most databases, this minimal set of files is enough. However, when the database contains big amounts...