Datasheet

just holds the schema and stored procedures needed to run your instance. You should always back up
the
master database after creating a new database, adding a login, or changing the configuration of the
server.
You should never create objects in the master database. If you create objects here, it may cause you to
have to make more frequent backups.
Tempdb Database
The tempdb database is like your database’s swap file. It’s used to hold temporary objects for all logins,
and the server may use the database to hold row-version information or system temporary objects. The
tempdb database is created each time you restart SQL Server. The database will be recreated to be its
original database size when the SQL Server is stopped. Since the database is recreated each time, there is
no reason to back it up. When you create a temporary object in the
tempdb database, it writes minimal
information into the log file. It is important to have enough space allocated to your
tempdb database,
because many operations that you will use in your database applications use the
tempdb. Generally
speaking, you should set
tempdb to autogrow as it needs space. If there is not enough space, the user
may receive one of the following errors:
❑ 1101 or 1105: The session connecting to SQL Server must allocate space in tempdb.
❑ 3959: The version store is full.
❑ 3967: The version store must shrink because tempdb is full.
Model Database
model is a system database that serves as a template when SQL Server creates a new database. As each
database is created, the first step is to copy the objects out of the
model database and into the empty
shell of the new database. The only time this does not apply is when you restore or attach a database
from a different server.
You can add objects or adjust the settings of the model database so that any subsequent databases will
have those properties set or contain those objects.
msdb Database
msdb is a system database that contains information used by SQL Server agent, log shipping, SSIS, and
the backup and restore system for the relational database engine. The database stores all the information
about jobs, operators, alerts, and job history. Because it contains this important system-level data, you
should back up this database regularly.
Schemas
Schemas enable you to compartmentalize database objects into groups based on their purpose. For
example, you may create a schema called
HumanResource and place all your employee tables and
stored procedures into that schema. You could then protect that schema to prevent users from seeing
data from within the schema. Think of a schema as a logical grouping of objects within a database.
When you call an object from within a schema, you use a two-part name at a minimum. You may be famil-
iar with the
dbo schema, which is the default schema for a given database. An Employee table in the default
7
SQL Server 2005 Architecture
04_055200 ch01.qxp 10/31/06 12:37 PM Page 7