What Makes Up a SQL Server Database?

A database is a storage structure for database objects. It is made up of at least two files. One file is referred to as a data file, and stores the database objects, such as tables and indexes. The second file is the transaction log file that records changes to the data. A data or log file can belong to only one database, and if filegroups are created within a database, each data file can be associated with only one database filegroup.

When a new database is created, its name and other key information are recorded in the sysdatabases table in the master database. The new database will also contain the system objects required for the database to function, primarily the system catalog tables. These system objects are copied from the model database at the time the database is created. Because all objects in the model database are copied when the database is created, the initial size of a database must be at least the size of the model database.

NOTE

You can leverage the fact that all objects in model are copied to the new database by creating any objects that you want to appear in all databases in the model database. This is a great way to propagate common objects, such as user-defined datatypes, user-defined database roles, and user -defined functions.