Checking System and Sample Databases

A new SQL Server installation includes the system databases listed in Table 7-3. System databases are critical to the proper operation of SQL Server, and an important aspect of the administration process is backing up and maintaining these databases. Sample databases can also be installed, but they are meant only to provide examples and do not need regular maintenance. You can locate and download the examples and sample databases from the Microsoft SQL Server Community & Samples Web site at http://www.codeplex.com/sqlserversamples. In the Microsoft Product Samples section, you’ll find links to samples for the SQL Server components and to the official SQL Server sample databases. If you install the samples, the sample files are installed by default in a component-specific subfolder of C:\Program Files\Microsoft SQL Server\100\Samples.

Table 7-3. Summary of System Databases

Database Name	Database Type	Description
master	System	Maintains information on all databases installed on the server. This database is modified anytime you create databases, manage accounts, or change configuration settings. Back up the master database regularly.
model	System	Provides a template for all new databases. If you want new databases to have certain properties or permissions, put these changes in the model database, and then all new databases will inherit the changes.
tempdb	System	Provides a temporary workspace for processing queries and handling other tasks. This database is re-created each time SQL Server is started, and it is based on the model database.
msdb	System	Used by the SQL Server Agent services when handling alerts, notifications, and scheduled tasks. You can access all the information in this database using SQL Server Management Studio options.
distribution	System/Replication	Used by Replication Services when you configure a server as a publisher, distributor, or both. This database is created when you configure replication, and it is not created automatically with a new installation.