Data structures and transaction logging
We can think about a database as of physical database structure consisting of tables and indexes. However, this is just a human point of view. From the SQL Server's perspective, a database is a set of precisely structured files described in a form of metadata also saved in database structures.
A conceptual imagination of how every database works is very helpful when the database has 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 lots of databases, this minimal set of files is not enough. When the database contains big amounts of data such as historical tables, or the database has big data contention such as production tracking systems, it's good practise to design more data files. Another situation when a basic set of files is not sufficient can arise when documents or pictures would be saved along...
